Spire.Xls我就不多介紹了, 百度一搜一堆, 我這里主要實(shí)現(xiàn)的是功能
程序需要引入 Spire.Xls DLL和其他相關(guān)幾個(gè)DLL
/// <summary>
/// Excel幫助類
/// </summary>
public class ExcelHelpers
{
#region 導(dǎo)入
/// <summary>
/// 將Excel以文件流轉(zhuǎn)換DataTable
/// </summary>
/// <param name="hasTitle">是否有表頭</param>
/// <param name="path">文件路徑</param>
/// <param name="tableindex">文件簿索引</param>
public static DataTable ExcelToDataTableFormPath(bool hasTitle = true, string path = "", int tableindex = 0)
{
//新建Workbook
Workbook workbook = new Workbook();
//將當(dāng)前路徑下的文件內(nèi)容讀取到workbook對(duì)象里面
workbook.LoadFromFile(path);
//得到第一個(gè)Sheet頁
Worksheet sheet = workbook.Worksheets[tableindex];
return SheetToDataTable(hasTitle, sheet);
}
/// <summary>
/// 將Excel以文件流轉(zhuǎn)換DataTable
/// </summary>
/// <param name="hasTitle">是否有表頭</param>
/// <param name="stream">文件流</param>
/// <param name="tableindex">文件簿索引</param>
public static DataTable ExcelToDataTableFormStream(bool hasTitle = true, Stream stream = null,int tableindex = 0)
{
//新建Workbook
Workbook workbook = new Workbook();
//將文件流內(nèi)容讀取到workbook對(duì)象里面
workbook.LoadFromStream(stream);
//得到第一個(gè)Sheet頁
Worksheet sheet = workbook.Worksheets[tableindex];
int iRowCount = sheet.Rows.Length;
int iColCount = sheet.Columns.Length;
DataTable dt = new DataTable();
//生成列頭
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = sheet.Range[1, i + 1].Text;
if (!string.IsNullOrEmpty(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1";//重復(fù)行名稱會(huì)報(bào)錯(cuò)。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行數(shù)據(jù)
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
dr[iCol - 1] = sheet.Range[iRow, iCol].Text;
}
dt.Rows.Add(dr);
}
return SheetToDataTable(hasTitle,sheet);
}
private static DataTable SheetToDataTable(bool hasTitle,Worksheet sheet)
{
int iRowCount = sheet.Rows.Length;
int iColCount = sheet.Columns.Length;
var dt = new DataTable();
//生成列頭
for (var i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = sheet.Range[1, i + 1].Text;
if (!string.IsNullOrEmpty(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1";//重復(fù)行名稱會(huì)報(bào)錯(cuò)。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行數(shù)據(jù)
// ReSharper disable once SuggestVarOrType_BuiltInTypes
var rowIdx = hasTitle ? 2 : 1;
for (var iRow = rowIdx; iRow <= iRowCount; iRow++)
{
var dr = dt.NewRow();
for (var iCol = 1; iCol <= iColCount; iCol++)
{
dr[iCol - 1] = sheet.Range[iRow, iCol].Text;
}
dt.Rows.Add(dr);
}
return dt;
}
#endregion
#region 導(dǎo)出
/// <summary>
/// 將DaTaTable轉(zhuǎn)成byte[]類型
/// </summary>
/// <param name="dt"></param>
/// <param name="hasTitle">是否有表頭</param>
/// <returns></returns>
public static byte[] GetDataTableToByte(DataTable dt,bool hasTitle)
{
using (MemoryStream ms = new MemoryStream())
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];//第一個(gè)工作簿
if (hasTitle) //表頭
{
for (var j = 0; j < dt.Columns.Count; j++)
{
sheet.Range[1, j + 1].Text = dt.Columns[j].ColumnName;
sheet.Range[1, j + 1].ColumnWidth = 22;
sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;//邊框
sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;//邊框
sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;//邊框
sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;//邊框
}
}
//循環(huán)表數(shù)據(jù)
for (var i = 0; i < dt.Rows.Count; i++)//循環(huán)賦值
{
for (var j = 0; j < dt.Columns.Count; j++)
{
var dyg = sheet.Range[i + 2, j + 1];
dyg.Text = dt.Rows[i][j].ToString();
dyg.ColumnWidth = 22;
dyg.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;//邊框
dyg.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
dyg.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
dyg.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
}
}
workbook.SaveToStream(ms, FileFormat.Version2007);
byte[] data = ms.ToArray();
return data;
}
}
#endregion
}
更多導(dǎo)出樣式請(qǐng)看:
http://www.cnblogs.com/landeanfen/p/5888973.html
