SoFunction
Updated on 2025-04-06

npoi2.0 converts datatable object to excel2007 example


using ;
using ;
using ;
        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 = ();
                headerRow = (XSSFRow)(0);
                foreach (DataColumn column in )
                    ().SetCellValue();
                int rowIndex = 1;
                foreach (DataRow row in )
                {
                    XSSFRow dataRow = (XSSFRow)(rowIndex);
                    foreach (DataColumn column in )
                        ().SetCellValue(row[column].ToString());
                    ++rowIndex;
                }
//Column width adaptable, only valid for English and numbers
                for (int i = 0; i <= ; ++i)
                    (i);
                (ms);
                ();
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                ();
                sheet = null;
                headerRow = null;
                workbook = null;
            }
            return ms;
        }
        private void DownloadExcel(DataTable dt,string reportName)
        {
                Stream s = RenderDataTableToExcel(dt);
                if (s != null)
                {
                    MemoryStream ms = as MemoryStream;
                    ("Content-Disposition", ("attachment;filename=" + (reportName) + ("yyyyMMdd") + ".xlsx"));
                    ("Content-Length", ().());
                    (());
                    ();
                    ();
                    ();
                }
else
("Error occurred, unable to download!");
        }