//excel导出，最多只能导出65535条记录，没有考虑分sheet页，后续如果数据量较多再扩充
public static void writeExcel(DataTable dt, ExcelInfo ei, String[] colTitle, int[] colWidth)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ei.sheetName);
HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);
for (int j = 0; j < colTitle.Length;j++)
{
//设置列宽
sheet.SetColumnWidth(j, colWidth[j] * 255);
rowtitle.CreateCell(j).SetCellValue(colTitle[j]);
}
int i = 1;
foreach (DataRow row in dt.Rows)
{
HSSFRow newrow = (HSSFRow)sheet.CreateRow(i);
int col = 0;
for (; col < dt.Columns.Count; col++)
newrow.CreateCell(col).SetCellValue(Convert.ToString(row[col]));
i++;
}
try
{
using (Stream stream = File.Open(ei.filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
}
catch (Exception e)
{
throw e;
}
}
//excel读取，没有考虑大数据量的情况，一次性将数据读取进内存，建议导入记录一次不多于1000条
private static DataTable readExcel(ExcelInfo ei, DataTable dt)
{
HSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(ei.filePath, FileMode.Open, FileAccess.Read))
{
//初始化一个工作簿对象
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
//获取数据页
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(ei.dataSheetIdx);
//得到数据行
System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); ;
for (int k = 0; k < ei.dataRowIdx; k++)
rows.MoveNext();
HSSFRow row = null;
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
public static DataTable transExcelToDT(ExcelInfo ei, DataTable dt)
{
//读取excel，将数据封装到DT
dt = readExcel(ei, dt);
return dt;
}
