用NPOI从DataBase到Excel '2

时间:2021-10-14 08:12:45

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();
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; IFont font = wk.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
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 = ;
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,,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,,data.Length);
fs.Flush();
data = null;
}
}