Java中这个类库叫POI,C#中叫NPOI,很多从Java一直到.Net平台的类库为了区别大部分都是在前面加个N,比如Hibernate和NHibernate。
一、使用NPOI下载大致分以下步骤:
1、创建workbook
2、创建sheet
3、创建row和cell
4、填充数据
5、设置cell的样式和字体
二、HSSFWorkbook和XSSFWorkbook 两者用法基本一样
HSSFWorkbook 创建的是Excel2003
HSSFSheet、HSSFCellStyle、HSSFFont等
XSSFWorkbook 创建的是Excel2007
XSSFSheet、XSSFCellStyle、XSSFFont等
三、行或列合并
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0)); //起始行 结束行 起始列 结束列
sheet.GetRow(0).GetCell(0).SetCellValue("姓名");//赋值
赋值时必须使用合并后左上角单元格的行列坐标
四、代码(以Excel2003为例):
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
public ActionResult Index() { List<string> headList = new List<string>(); headList.Add("语文"); headList.Add("数学"); headList.Add("英语"); headList.Add("政治"); List<string> nameList = new List<string>(); nameList.Add("张三"); nameList.Add("李四"); nameList.Add("王五"); nameList.Add("赵六"); nameList.Add("胜七"); nameList.Add("朱重八"); nameList.Add("九喇嘛"); MemoryStream memoryStream = new MemoryStream(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); //创建单元格设置对象 HSSFCellStyle cellStyle = workbook.CreateCellStyle(); //设置水平、垂直居中 cellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER; cellStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER; //单元格填充颜色 cellStyle.FillForegroundColor = HSSFColor.PALE_BLUE.index; cellStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND; //设置边框 cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN; cellStyle.BorderLeft = HSSFCellStyle.BORDER_THIN; cellStyle.BorderRight = HSSFCellStyle.BORDER_THIN; cellStyle.BorderTop = HSSFCellStyle.BORDER_THIN; //创建设置字体对象 HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 16;//设置字体大小 font.Boldweight = short.MaxValue; //加粗 font.FontName = "宋体"; cellStyle.SetFont(font); //创建Excel行和单元格 for (int i = 0; i < nameList.Count + 2; i++) { HSSFRow newRow = sheet.CreateRow(i); for (int j = 0; j <headList.Count+1; j++) { newRow.CreateCell(j); } } //设置Excel表头 sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0)); //起始行 结束行 起始列 结束列 sheet.GetRow(0).GetCell(0).CellStyle = cellStyle; sheet.GetRow(1).GetCell(0).CellStyle = cellStyle; sheet.GetRow(0).GetCell(0).SetCellValue("姓名");//姓名 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 4)); //起始行 结束行 起始列 结束列 sheet.GetRow(0).GetCell(1).CellStyle = cellStyle; sheet.GetRow(0).GetCell(4).CellStyle = cellStyle; sheet.GetRow(0).GetCell(1).SetCellValue("课程");//课程 for (int i = 0; i < headList.Count; i++) { sheet.GetRow(1).GetCell(i + 1).CellStyle = cellStyle; sheet.GetRow(1).GetCell(i + 1).SetCellValue(headList[i]);//具体课程 } //导入数据 for (int i = 0; i < nameList.Count; i++) { sheet.GetRow(i + 2).GetCell(0).SetCellValue(nameList[i]); for (int j = 0; j < 4; j++) { sheet.GetRow(i + 2).GetCell(j+1).SetCellValue(i*j); } } workbook.Write(memoryStream); memoryStream.Seek(0,SeekOrigin.Begin); Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode("test.xls")); Response.ContentType = "application/octet-stream"; Response.Charset = "gb2312"; Response.ContentEncoding = Encoding.UTF8; Response.BinaryWrite(memoryStream.GetBuffer()); Response.Flush(); Response.End(); return new EmptyResult(); }
最后结果:
转载请注明出处!