当前环境.net4.0
去官方下下载: NOPI官网
关于NOPI的详细，这里就不再介绍。
在项目中，我们只需引入 NPOI.dll 就可以了.
接下来................................................................
上代码......... ..........................................................
/// <summary>
/// 导出
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="filePath">导出地址</param>
public static void ExportExcel(DataTable dt, string filePath)
{
try
{ //创建文档
IWorkbook workbook = new HSSFWorkbook();
//创建表格
ISheet sheet = workbook.CreateSheet(dt.TableName) as NPOI.HSSF.UserModel.HSSFSheet;
//第0行合并单元格，并且插入标题
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count));
//IRow row1 = sheet.CreateRow(0);
////row1.CreateCell(0).SetCellValue(dt.TableName);
//ICell col = row1.CreateCell(0);
//col.SetCellValue(dt.TableName);
//col.CellStyle.Alignment = HorizontalAlignment.Center;
//col.CellStyle.VerticalAlignment = VerticalAlignment.Center;
//col.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index; //从第一行获取和插入列名
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //从第二行中开始插入数据
int index = ;
foreach (DataRow rows in dt.Rows)
{
row = sheet.CreateRow(index);
foreach (DataColumn item in dt.Columns)
{
ICell cel= row.CreateCell(item.Ordinal);
cel.SetCellValue(rows[item].ToString()); } index++;
} MemoryStream ms = new MemoryStream();
workbook.Write(ms); using (FileStream fileStream = new FileStream(filePath, FileMode.OpenOrCreate))
{
// workbook.Write(fileStream);
// fileStream.Flush(); byte[] bArr = ms.ToArray();
fileStream.Write(bArr, , bArr.Length);
fileStream.Flush(); }
workbook.Close();
}
catch (Exception ex)
{ throw ex;
}
}
具体使用方法如下：
数据是模拟数据..........
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable("学生表");
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int)); for (int i = ; i <= ; i++)
{ DataRow row = dt.NewRow();
row["ID"] = i;
row["Name"] = "名字" + i;
row["Age"] = i;
dt.Rows.Add(row);
}
//导出
ExportExcel(dt, @"d:\xxx2.xls");
Console.WriteLine("OK");
Console.ReadKey();
}
接下来是导入方法。
/// <summary>
/// 导入
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="sheetIndex">sheet索引，默认为第一个</param>
/// <returns></returns>
public static DataTable ImportExcel(string filePath, int sheetIndex=)
{
try
{
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
{
DataTable dt = new DataTable();
IWorkbook workbook = new HSSFWorkbook(fs); // sheet索引，默认为第一个
ISheet sheet = workbook.GetSheetAt(sheetIndex); //读取第一行列名
foreach (ICell cels in sheet.GetRow(sheet.FirstRowNum).Cells)
{
dt.Columns.Add(cels.ToString());
} //第二行开始
for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
IRow rows = sheet.GetRow(i);
DataRow dataRow = dt.NewRow(); //遍历每行每列的值
for (int j = rows.FirstCellNum; j < rows.LastCellNum; j++)
{
dataRow[j] = rows.GetCell(j).ToString();
} dt.Rows.Add(dataRow);
} workbook.Close(); return dt; }
}
catch (Exception ex)
{ throw ex;
} }
使用方法如下：
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable("学生表");
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int)); for (int i = ; i <= ; i++)
{ DataRow row = dt.NewRow();
row["ID"] = i;
row["Name"] = "名字" + i;
row["Age"] = i;
dt.Rows.Add(row);
}
//导入并且返回DataTable,您可以选择用sqlbulkCopy批量插入数据库
var data= ImportExcel(@"d:\xxx2.xls");
Console.WriteLine("OK");
Console.ReadKey();
关于sqlbulkCopy批量插入数据库，效率还是挺高的，详情可以看我上一篇文章。
本文到此结束。