try
{
string TempletFileName = @"D:\template.xls";
File.Copy(TempletFileName, targetPath, true);
HSSFWorkbook wk = null;
using (FileStream fs = File.Open(targetPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里,之后就可以关闭了
wk = new HSSFWorkbook(fs);
}
HSSFSheet sheet1 = (HSSFSheet)wk.GetSheetAt(0);
//插入行,并复制第一行的样式
sheet1.ShiftRows(3, exportTable.Rows.Count + 3, exportTable.Rows.Count, true, false);
var rowSource = sheet1.GetRow(2);
var rowStyle = rowSource.RowStyle;//获取当前行样式
for (int i = 3; i < exportTable.Rows.Count + 3; i++)
{
var rowInsert = sheet1.CreateRow(i);
if (rowStyle != null)
rowInsert.RowStyle = rowStyle;
rowInsert.Height = rowSource.Height;
for (int col = 0; col < rowSource.LastCellNum; col++)
{
var cellsource = rowSource.GetCell(col);
var cellInsert = rowInsert.CreateCell(col);
var cellStyle = cellsource.CellStyle;
//设置单元格样式
if (cellStyle != null)
{
cellInsert.CellStyle = cellsource.CellStyle;
}
}
}
if (exportTable != null)
{
int nRow = 2;
string nextFirstTxt = string.Empty;
for (int i = 0; i < exportTable.Rows.Count; i++)
{
//IRow row = sheet1.CreateRow(nRow);
IRow row = sheet1.GetRow(nRow);
DataRow dr = exportTable.Rows[i];
//以下为数据库中查询出的字段名称
row.Cells[0].SetCellValue(dr["User"].ToString());
row.Cells[2].SetCellValue(dr["Tel"].ToString());
row.Cells[3].SetCellValue(dr["Email"].ToString());
row.Cells[5].SetCellValue(dr["OrderStatus"].ToString());
row.Cells[6].SetCellValue(dr["PostAddress"].ToString());
row.Cells[7].SetCellValue(dr["EmailCode"].ToString());
nRow++;
}
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(targetPath, FileMode.Create, FileAccess.Write))
{
wk.Write(fs);
}
}
MessageBox.Show("导出成功");
}
catch (Exception ex)
{
MessageBox.Show("导出失败");
}
//以下为模板文件