npoi2.0将datatable对象转换为excel2007示例

npoi 2.0将datatable对象转换为excel 2007文件提供下载

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;
                }
                //列宽自适应，只对英文和数字有效
                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("出错，无法下载！");
        }