NPOI 操作Excel

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel;   //导入  public void upload(HttpPostedFileBase file)         {             if (file != null)             {                 string houzui = System.IO.Path.GetExtension(file.FileName).ToLower();                 byte[] fileBytes = new byte[file.ContentLength];                 file.InputStream.Read(fileBytes, 0, file.ContentLength);                 MemoryStream excelFileStream = new MemoryStream(fileBytes);                 IWorkbook workbook = null;                 if (houzui == ".xls")                 {                     workbook = new HSSFWorkbook(excelFileStream);                 }                 else if (houzui == ".xlsx")                 {                     workbook = new XSSFWorkbook(excelFileStream);                 }                 ISheet sheet = workbook.GetSheetAt(0);                 //行数                 int rowCount = sheet.LastRowNum;                 //第一行第一列                 sheet.GetRow(0).GetCell(0);             }         }

　　

  public FileResult Export()
        {
                // 创建工作簿
                var workbook = new HSSFWorkbook();
                // 创建表
                var sheet = workbook.CreateSheet();
                //设置列宽
                sheet.SetColumnWidth(0, 25 * 256);
                sheet.SetColumnWidth(1, 140 * 256);
                sheet.SetColumnWidth(2, 15 * 256);
                // CreateFont：创建字体样式
                var headFont = workbook.CreateFont();
                headFont.IsBold = true;

                // CreateCellStyle：创建单元格样式
                var headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center; 
                headStyle.VerticalAlignment = VerticalAlignment.Center;
                headStyle.SetFont(headFont);

                // CreateRow：操作指定表的指定行。
                var rowIndex = 0;
                var row = sheet.CreateRow(rowIndex);
                row.Height = 20 * 20;
                ICell cell; ;
                List<string> titles = new List<string>() { "编码", "标题1", "标题2", "开始时间", "结束时间" };
                for (int i = 0; i < titles.Count; i++)
                {
                    cell = row.CreateCell(i);
                    // 为单元格赋予值和样式。此处为表头
                    cell.SetCellValue(titles[i]);
                    cell.CellStyle = headStyle;
                }

                // 实际数据部分的单元格样式
                var cellStyle = workbook.CreateCellStyle();

                foreach (var item in fxlist)
                {
                    row = sheet.CreateRow(++rowIndex);
                    //行高
                    row.Height = 20 * 20;
                    cell = row.CreateCell(0);
                    cell.SetCellValue(item.ProjectCode);
                    cell.CellStyle = cellStyle;

                    cell = row.CreateCell(1);
                    cell.SetCellValue(GetProjectFullName(list, item.ProjectCode));
                    cell.CellStyle = cellStyle;
                }


                // bs用于存储文件数据的字节数组，一般给前端下载的文件都需要用这种方式传递
                byte[] bs;
                // 导出内存流并通过内存流读取为byte[]
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    bs = ms.GetBuffer();
                }
                // 返回给前端下载：File(存储文件数据的字节数组, 对应文件content-type, 文件名)
                return File(bs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "BIM形象进度设置模板.xls");
            
        }