npoi2.0 converts the datatable object to the excel2007 example

  • 2020-06-19 11:32:54
  • OfStack

NPOI 2.0 converts DataTable objects to Excel 2007 files for download


using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
        private Stream RenderDataTableToExcel(DataTable SourceTable)
        {
            XSSFWorkbook workbook = null;
            MemoryStream ms = null;
            ISheet sheet = null;
            XSSFRow headerRow = null;
            try
            {
                workbook = new XSSFWorkbook();
                ms = new MemoryStream();
                sheet = workbook.CreateSheet();
                headerRow = (XSSFRow)sheet.CreateRow(0);
                foreach (DataColumn column in SourceTable.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                int rowIndex = 1;
                foreach (DataRow row in SourceTable.Rows)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in SourceTable.Columns)
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    ++rowIndex;
                }
                // Column width is adaptive, valid only for English and Numbers 
                for (int i = 0; i <= SourceTable.Columns.Count; ++i)
                    sheet.AutoSizeColumn(i);
                workbook.Write(ms);
                ms.Flush();
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                ms.Close();
                sheet = null;
                headerRow = null;
                workbook = null;
            }
            return ms;
        }
        private void DownloadExcel(DataTable dt,string reportName)
        {
                Stream s = RenderDataTableToExcel(dt);
                if (s != null)
                {
                    MemoryStream ms = resultStream.result as MemoryStream;
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"));
                    Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                    Response.BinaryWrite(ms.ToArray());
                    Response.Flush();
                    ms.Close();
                    ms.Dispose();
                }
else
Response.Write(" Error, unable to download! ");
        }


Related articles: