普通的方式Excel的读取和写入,如果遇到大数据量的操作,效率会很低,NPOI组件可以解决这个问题.
一、通过NPOI组件获取Excel所有表(Sheet)名
public List<String> GetSheetName(){
using (FileStream file = new FileStream(ExcelName, FileMode.Open, FileAccess.Read))
{
List<String> LstName=new List<String>();
IWorkbook workbook;String hz = Path.GetExtension(ExcelName);
if (hz.Equals(".xls")) //针对2003版本
workbook = new HSSFWorkbook(file);
else
workbook = new XSSFWorkbook(file);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
LstName.Add(workbook.GetSheetName(i));
}
}
return LstName;
}
二、通过NPOI组件读取某个sheet的数据至DataTable
private System.Data.DataTable GetDataFromExcel(string sheetname)
{
System.Data.DataTable dt = null;
using (FileStream file = new FileStream(ExcelName, FileMode.Open, FileAccess.Read))
{
dt = new System.Data.DataTable();
String hz = Path.GetExtension(ExcelName);
IWorkbook workbook;
if (hz.Equals(".xls")) //针对2003版本
workbook = new HSSFWorkbook(file);
else
workbook = new XSSFWorkbook(file);
ISheet sheet = workbook.GetSheet(sheetname);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)
dt.Columns.Add("A" + (i + 1).ToString());
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
三、通过NPOI组件将Datatable的数据导出至Excel
public bool DataToExcel(DataTable SourceDataTable )
{
if (SourceDataTable == null) return false;
if (SourceDataTable.Rows.Count == 0) return false;
MemoryStream ms = new MemoryStream();
try
{
using (SourceDataTable)
{
String hz = Path.GetExtension(ExcelName);
IWorkbook workbook;
if (hz.Equals(".xls")) //针对2003版本
workbook = new HSSFWorkbook();
else
workbook = new XSSFWorkbook();
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "宋体";
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.SetFont(font);
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in SourceDataTable.Columns)
{
sheet.SetColumnWidth(column.Ordinal, 10 * 256);
sheet.SetDefaultColumnStyle(column.Ordinal, cellStyle);
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
}
// handling value.
int rowIndex = 0;
foreach (DataRow row in SourceDataTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.RowStyle = cellStyle;
foreach (DataColumn column in SourceDataTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
File.Delete(ExcelName);
using (FileStream fs = new FileStream(ExcelName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
return true;
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return false;
}
}
四、NPOI其他属性
sheet.PhysicalNumberOfRows 最大行数
sheet.PhysicalNumberOfColumns 最大列数