C#教程之用NPOI从DataBase到Excel '2

NPOI的C# Helper代码2

 

        public static MemoryStream ExportXls(DataTable dt)
        {
            HSSFWorkbook wk = new HSSFWorkbook();
            ISheet sheet = null;

            string sheetName = "Sheet1";
            if (!string.IsNullOrEmpty(dt.TableName))
            {
                sheetName = dt.TableName;
            }
            sheet = wk.CreateSheet(sheetName);
            //列头及样式
            IRow headerRow = sheet.CreateRow(0);
            ICellStyle headStyle = wk.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;

            IFont font = wk.CreateFont();
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;
            font.FontName = "微雅黑体";
            headStyle.SetFont(font);
            
            foreach (DataColumn column in dt.Columns)  //column共属性
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //clomun.ColumnName
                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;  //体会
            }

            int rowIndex = 1;
            foreach (DataRow row in dt.Rows)
            {
                //HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); //也可以这样写  
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
            MemoryStream ms = new MemoryStream();
            wk.Write(ms);
            ms.Flush();
            return ms;
        }

        //输出
        public static void SaveToFile(MemoryStream ms) 
        {
            using (FileStream fs = File.OpenWrite(@"c:\1.xls"))
            {
                byte[] data = ms.ToArray();
                fs.Write(data,0,data.Length);
                fs.Flush();
                data = null;
            }
        }
        public static void SaveToFile(MemoryStream ms, string filePath)
        {
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data,0,data.Length);
                fs.Flush();
                data = null;
            }
        }