//为了处理计算和之类的补公式

public delegate void WriteXlsEndDelegate(DataTable dataTable, HSSFSheet sheet);  //代理函数方式

public WriteXlsEndDelegate writeXlsEndDelegate=null;

/*

 * 根据模板导出xls

 * 数据表，文件名，模板文件名，填充开始行，原始模板里的数据表行数（不包括表头、求和行，一般要留一条示例数据)

 */

public void ExportXlsByTemplate(DataTable dataTable, string filename, string templatefile, int startDataRegionRow,int orgRowCount=)

{

    HttpContext context = HttpContext.Current;

    //Excel模板

    string templetFileName = context.Server.MapPath(templatefile);

    HSSFWorkbook wk = null;

    if (templetFileName != null)

    { using (FileStream fs = File.Open(templetFileName, FileMode.Open,

            FileAccess.Read, FileShare.ReadWrite))

        {

            //把xls文件读入workbook变量里，之后就可以关闭了

            wk = new HSSFWorkbook(fs);

            fs.Close();

        }

    }

    else

    {

        return;

    }

    if (dataTable.Rows.Count>)

    {

        HSSFSheet sheet1 = (HSSFSheet)wk.GetSheetAt();

        var rowSource = sheet1.GetRow(startDataRegionRow);

        var rowStyle = rowSource.RowStyle;//获取当前行样式

        sheet1.ShiftRows(startDataRegionRow + orgRowCount, startDataRegionRow + orgRowCount, dataTable.Rows.Count - orgRowCount, true, false);//从下一行开始移动

        for (int i = startDataRegionRow + orgRowCount; i < startDataRegionRow + orgRowCount + (dataTable.Rows.Count - orgRowCount); i++)

        {

            var rowInsert = sheet1.CreateRow(i);

            if (rowStyle != null)

                rowInsert.RowStyle = rowStyle;

            rowInsert.Height = rowSource.Height;

            for (int col = ; col < rowSource.LastCellNum; col++)

            {

                var cellsource = rowSource.GetCell(col);

                var cellInsert = rowInsert.CreateCell(col,cellsource.CellType);

                var cellStyle = cellsource.CellStyle;

                //设置单元格样式　　　　

                if (cellStyle != null)

                    cellInsert.CellStyle = cellsource.CellStyle;

                cellInsert.SetCellValue("");

            }

        }

        string nextFirstTxt = string.Empty;

        for (int i = startDataRegionRow; i < startDataRegionRow + dataTable.Rows.Count; i++)

        {

            IRow row = sheet1.GetRow(i);

            for (int j = ; j < dataTable.Columns.Count; j++)

            {

                var drValue = dataTable.Rows[i - startDataRegionRow][j].ToString();

                    switch (dataTable.Columns[j].DataType.ToString())

                    {

                        case "System.String"://字符串类型

                            row.GetCell(j).SetCellValue(drValue);

                            break;

                        case "System.DateTime"://日期类型

                            DateTime dateV;

                            DateTime.TryParse(drValue, out dateV);

                            row.GetCell(j).SetCellValue(dateV);

                            break;

                        case "System.Boolean"://布尔型

                            bool boolV = false;

                            bool.TryParse(drValue, out boolV);

                            row.GetCell(j).SetCellValue(boolV);

                            break;

                        case "System.Int16"://整型

                        case "System.Int32"://整型

                        case "System.Int64"://整型

                        case "System.Int"://整型

                            int invV = ;

                            int.TryParse(drValue, out invV);

                            row.GetCell(j).SetCellValue(invV);

                            break;

                        case "System.Byte":

                            int intV = ;

                            int.TryParse(drValue, out intV);

                            row.GetCell(j).SetCellValue(intV);

                            break;

                        case "System.Decimal"://浮点型

                        case "System.Double":

                            double doubV = ;

                            double.TryParse(drValue, out doubV);

                            row.GetCell(j).SetCellValue(doubV);

                            break;default:

                            row.GetCell(j).SetCellValue("");

                            break;

                    }

            }

        }

        sheet1.ForceFormulaRecalculation = true;

        if(writeXlsEndDelegate!=null)//结束时是否需要进行其它处理

            writeXlsEndDelegate(dataTable,sheet1);

    }

    context.Response.ContentType = "application/vnd.ms-excel";

    // 添加头信息，指定文件名格式

    context.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);

    context.Response.AddHeader("Content-Transfer-Encoding", "binary");

    context.Response.ContentType = "application/octet-stream";

    context.Response.ContentEncoding = System.Text.Encoding.UTF8;

    MemoryStream file = new MemoryStream();

    wk.Write(file);

    context.Response.BinaryWrite(file.GetBuffer());

}

ClassTest npoi=new ClassTest();
//设置公式

npoi.writeXlsEndDelegate = (dataTable1, sheet) =>

{

    int rowIndex = dataTable1.Rows.Count + ;

    ///做后补操作

};

npoi.ExportXlsByTemplate(dataTable, filename, @"/Template/xls/test.xls", );