excel npoi 连接_asp.net NPOI导入Excel(兼容xls\xlsx) Or 导出Excel

#region 导出方法(NPOI)

///

///合并单元格///

///

///

///

///

///

private void mergeCell(ISheet sheet, int firstRow, int lastRow, int firstCell, intlastCell)

{

sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCell, lastCell));//2.0使用 2.0以下为Region

}//Datatable导出Excel

private void GridToExcelByNPOI(System.Data.DataTable dt, string strExcelFileName, stringtitle)

{try{

dt.Columns["row"].SetOrdinal(0);

dt.Columns["row"].ColumnName = "序号";

}catch{ }

HSSFWorkbook workbook= null;try{

workbook= newHSSFWorkbook();

ISheet sheet= workbook.CreateSheet("Sheet1");

ICellStyle HeadercellStyle=workbook.CreateCellStyle();

HeadercellStyle.BorderBottom=NPOI.SS.UserModel.BorderStyle.Thin;

HeadercellStyle.BorderLeft=NPOI.SS.UserModel.BorderStyle.Thin;

HeadercellStyle.BorderRight=NPOI.SS.UserModel.BorderStyle.Thin;

HeadercellStyle.BorderTop=NPOI.SS.UserModel.BorderStyle.Thin;

HeadercellStyle.Alignment=NPOI.SS.UserModel.HorizontalAlignment.Center;//字体

NPOI.SS.UserModel.IFont headerfont =workbook.CreateFont();

headerfont.Boldweight= (short)FontBoldWeight.Bold;

HeadercellStyle.SetFont(headerfont);

IRow headerTitle= sheet.CreateRow(0);

ICell cell1= headerTitle.CreateCell(0);

cell1.SetCellValue(title);

mergeCell(sheet,0, 0, 0, dt.Columns.Count - 1);//合并单元格

HSSFCellStyle fCellStyle=(HSSFCellStyle)workbook.CreateCellStyle();

HSSFFont ffont=(HSSFFont)workbook.CreateFont();

ffont.FontHeight= 20 * 20;

ffont.FontName= "宋体";//ffont.Color = HSSFColor.Red.Index;

fCellStyle.SetFont(ffont);

fCellStyle.VerticalAlignment= NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中

fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中

cell1.CellStyle =fCellStyle;//用column name 作为列名

int icolIndex = 0;

IRow headerRow= sheet.CreateRow(1);foreach (DataColumn item indt.Columns)

{

ICell cell=headerRow.CreateCell(icolIndex);

cell.SetCellValue(item.ColumnName);

cell.CellStyle=HeadercellStyle;

icolIndex++;

}

ICellStyle cellStyle=workbook.CreateCellStyle();//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看

cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");

cellStyle.BorderBottom=NPOI.SS.UserModel.BorderStyle.Thin;

cellStyle.BorderLeft=NPOI.SS.UserModel.BorderStyle.Thin;

cellStyle.BorderRight=NPOI.SS.UserModel.BorderStyle.Thin;

cellStyle.BorderTop=NPOI.SS.UserModel.BorderStyle.Thin;

NPOI.SS.UserModel.IFont cellfont=workbook.CreateFont();

cellfont.Boldweight= (short)FontBoldWeight.Normal;

cellStyle.SetFont(cellfont);//建立内容行

int iRowIndex = 2;int iCellIndex = 0;foreach (DataRow Rowitem indt.Rows)

{

IRow DataRow=sheet.CreateRow(iRowIndex);foreach (DataColumn Colitem indt.Columns)

{

ICell cell=DataRow.CreateCell(iCellIndex);try{if (Colitem.ColumnName.Contains("编号"))

{

cell.SetCellValue(Rowitem[Colitem].ToString());

}else{

cell.SetCellValue(Convert.ToDouble(Rowitem[Colitem].ToString()));

}

}catch{

cell.SetCellValue(Rowitem[Colitem].ToString());

}

cell.CellStyle=cellStyle;

iCellIndex++;

}

iCellIndex= 0;

iRowIndex++;

}//自适应列宽度

for (int i = 0; i < icolIndex; i++)

{

sheet.AutoSizeColumn(i);

}//设置Excel的自动筛选//CellRangeAddress c = CellRangeAddress.ValueOf("A1");//sheet.SetAutoFilter(c);//写Excel

MemoryStream ms = newMemoryStream();

workbook.Write(ms);

Response.AppendHeader("content-disposition", "attachment;filename=" +strExcelFileName);

Response.BinaryWrite(ms.ToArray());

workbook= null;

ms.Close();

ms.Dispose();

Response.End();

}catch(Exception ex)

{

ClientScript.RegisterStartupScript(GetType(),"type1", "");

}finally { if (workbook != null) workbook = null; }

}#endregion