首页 > 代码库 > NPOI之C#下载Excel

NPOI之C#下载Excel

Java中这个类库叫POI,C#中叫NPOI,很多从Java一直到.Net平台的类库为了区别大部分都是在前面加个N,比如Hibernate和NHibernate。

npoi下载地址

一、使用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();        }

 最后结果:

技术分享

 

转载请注明出处!

NPOI之C#下载Excel