Question

我用NPOI导出网页数据，如何将一张表的数据分2个或是更多个sheet导出？？

我用NPOI，然后使用了模板，模板sheet1能导出10条数据，但是当数据超过10条，小于等于20条时，我就要复制sheet1然后将后面的10条导出显示出来,这里就一共2个sheet，当20<数据<30,我就要在第3个sheet中显示这10条数据，这里一共就是3个sheet，以此类推下去。。。

Answer 1

#region private
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextDic">头文本</param>
/// <param name="fileName">文件名</param>
/// <param name="isShowTitle">是否显示表头</param>
/// <returns></returns>
private static MemoryStream Export(DataTable dtSource, Dictionary<string, string> headerTextDic, string fileName, bool isShowTitle)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

SetSummaryInformation(workbook);

HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

int[] arrColWidth = GetColWidth(dtSource, headerTextDic);

int rowIndex = 0;

HSSFCellStyle colHeaderStyle = GetColHeaderStyle(workbook);
HSSFCellStyle contentDataStyle = GetContentDataStyle(workbook);

foreach (DataRow row in dtSource.Rows)
{
if (rowIndex == 10|| rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
SetDataTitle(headerTextDic, fileName, isShowTitle, workbook, sheet);
SetColHeader(dtSource, headerTextDic, isShowTitle, workbook, sheet, arrColWidth, colHeaderStyle);
if (isShowTitle)
{
rowIndex = 2;
}
else
{
rowIndex = 1;
}
}

HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
SetContentData(dtSource, headerTextDic, dateStyle, row, dataRow, workbook, contentDataStyle);

rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;

// sheet.Dispose();
// workbook.Dispose();
return ms;
}
}

追问

我使用的模板，本身有一个sheet1可以保存10条数据，当10<数据<20保存到复制的sheet1命名为sheet2的sheet2中？？你这样写我看不明白。。

追答

不需要模板的，列名什么的都可以通过代码定义，模板反而太死板