NOPI技术实现 Excel表格多个sheet工作簿

1.直接上代码，下载NOPI  dll  地址：http://download.csdn.net/detail/qq_25580555/9576072

2.代码函数封装

#region NOPI导出Excel多个sheet         ///         /// 导出EXCEL,可以导出多个sheet         ///         /// 原始数据数组类型         /// 路径         public void ExportEasy(DataSet dtSources, string strFileName)         {             try             {                 NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();                 for (int k = 0; k < dtSources.Tables.Count; k++)                 {                     NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(dtSources.Tables[k].TableName.ToString());

                    //填充表头                     NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(0);

                    //特殊处理，需要添加一行数据

                    if (k == 1)                     {                         dataRow.CreateCell(0).SetCellValue("户号绑定时间：" + txt_Start.Value+"--"+txt_End.Value);

                        dataRow = sheet.CreateRow(1);

                        foreach (DataColumn column in dtSources.Tables[k].Columns)                         {                             dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                         }

                        //填充内容                         for (int i = 0; i < dtSources.Tables[k].Rows.Count; i++)                         {                             dataRow = sheet.CreateRow(i + 2);                             for (int j = 0; j < dtSources.Tables[k].Columns.Count; j++)                             {                                 dataRow.CreateCell(j).SetCellValue(dtSources.Tables[k].Rows[i][j].ToString());                             }                         }                     }                     else                     {                         foreach (DataColumn column in dtSources.Tables[k].Columns)                         {                             dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                         }

                        //填充内容                         for (int i = 0; i < dtSources.Tables[k].Rows.Count; i++)                         {                             dataRow = sheet.CreateRow(i + 1);                             for (int j = 0; j < dtSources.Tables[k].Columns.Count; j++)                             {                                 dataRow.CreateCell(j).SetCellValue(dtSources.Tables[k].Rows[i][j].ToString());                             }                         }                     }                                    }

                //保存                 using (MemoryStream ms = new MemoryStream())                 {                     //using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                     {

                        //System.IO.MemoryStream ms = new System.IO.MemoryStream();                         workbook.Write(ms);                         //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=绩效统计.xls"));                         Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(strFileName + ".xls"));//注意                         Response.BinaryWrite(ms.ToArray());                         workbook = null;                         ms.Close();                         ms.Dispose();

                        //workbook.Write(fs);                     }                 }             }             catch (Exception ex)             {                 Function.ShowMessage(ex.Message.ToString());             }

        }         ///         /// 导出单个EXCEL         ///         ///         ///         public static void ExportEasy(DataTable dtSource, string strFileName)         {             NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();             NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();

            //填充表头             NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(0);             foreach (DataColumn column in dtSource.Columns)             {                 dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);             }

            //填充内容             for (int i = 0; i < dtSource.Rows.Count; i++)             {                 dataRow = sheet.CreateRow(i + 1);                 for (int j = 0; j < dtSource.Columns.Count; j++)                 {                     dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());                 }             }

            //保存             using (MemoryStream ms = new MemoryStream())             {                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                 {                     workbook.Write(fs);                 }             }             //workbook.Dispose();

        }

        #endregion