聊城微信推广网站做网站学什么

当前位置： 首页 > news >正文

聊城微信推广网站,做网站学什么,网页设计html代码大全python,软装设计公司名字今天在弄使用npoi对excel表的操作#xff0c;遇到个问题就是使用workbook通过filestream打开后#xff0c;让后workbook.write(filestream)居然报文件流关闭了#xff0c;无法写入#xff0c;弄了好久都不行#xff0c;最后通过写2个excel文件来解决#xff0c;现在看来我…今天在弄使用npoi对excel表的操作遇到个问题就是使用workbook通过filestream打开后让后workbook.write(filestream)居然报文件流关闭了无法写入弄了好久都不行最后通过写2个excel文件来解决现在看来我使用 HSSFWorkbook workbook new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite));这种读写模式有问题使用这种  using (var stream new FileStream(filePath, FileMode.Open, FileAccess.Read))既可以读又可以写。 public void AppendDataToExistingExcel(string filePath) { try { IWorkbook workbook; using (var stream new FileStream(filePath, FileMode.Open, FileAccess.Read)) { workbook filePath.EndsWith(.xlsx) ? (IWorkbook)new XSSFWorkbook(stream) : new HSSFWorkbook(stream); } var sheet workbook.GetSheetAt(0); int lastRowNum sheet.LastRowNum; sheet.GetRow(0).CreateCell(0).SetCellValue(hahaha); using (var stream new FileStream(filePath, FileMode.Open, FileAccess.Write)) { workbook.Write(stream); } } catch (Exception ex) {} } Npoi的操作 New一个workbook,workbook通过createSheet()或者getSheetAt(index)获取sheet页面 sheet通过createRow(rowIndex)或者getRow(indexIndex)获取或者创建行 Sheet.lastRownum可以获取所有行 IRow row sheet.createRow(rowIndex);获取行 Row.capacity 或者row.cells.count获取每一行的列个数注意的是没有sheet的列个数只有行的列个数 当向一个行中的一个单元格写数据的时候row.createCell(colIndex),cell.setCellValue()如果没有向一个单元格写过数据那么row.getCell()会返回null的 Cell样式 注意style通过workbook.createStyle()而不是通过其他获得 有的api有font.isBold true;但是我这个没有只有通过font.BoldWeight800来设置注意是对每一个cell来设置样式 cell.cellStyle style;也就是for循环行和列来设置样式 合并单元格 只需要new CellRangeAddress()即可然后sheet.addMergendRegion(cellRangeAddress),这样就合并了但是合并后需要写入信息以及重新设置样式 public class WZExcelUtil     {         /// summary         /// 设置一般表格样式         /// /summary         /// param nameworkbook/param         /// param namesheetIndex/param         public void setNormalCellStyle(IWorkbook workbook, int sheetIndex)         { ICellStyle style workbook.CreateCellStyle();             var font workbook.CreateFont();             font.FontHeightInPoints 12;             //font.FontName Arial;             font.FontName 仿宋;             style.Alignment NPOI.SS.UserModel.HorizontalAlignment.Center;             style.VerticalAlignment NPOI.SS.UserModel.VerticalAlignment.Center;             style.SetFont(font); ISheet sheet workbook.GetSheetAt(sheetIndex);             int rowNum sheet.LastRowNum;             for (int rowIndex 0; rowIndex rowNum 1; rowIndex)             {                 IRow row sheet.GetRow(rowIndex);                 //int cols row.Cells.Capacity;                 int cols row.Cells.Count;                 for (int colIndex 0; colIndex cols; colIndex)                 {                     var cell sheet.GetRow(rowIndex).GetCell(colIndex);                     cell.CellStyle style;                     sheet.SetColumnWidth(colIndex, 15 * 256); // 设置第 1 列宽度为 20                 }                 row.HeightInPoints 25; // 设置行高为 25 点             }         } /// summary         /// 设置title表格样式         /// /summary         /// param nameworkbook/param         /// param namesheetIndex/param         /// param nameheadTitleIndex/param         public void setHeadTitleStyle(IWorkbook workbook, int sheetIndex, int headTitleIndex)         {             ISheet sheet workbook.GetSheetAt(sheetIndex); ICellStyle style workbook.CreateCellStyle();             var font workbook.CreateFont();             font.FontHeightInPoints 14;             //font.FontName Arial;             font.FontName 仿宋;             font.Boldweight 800;             style.Alignment NPOI.SS.UserModel.HorizontalAlignment.Center;             style.VerticalAlignment NPOI.SS.UserModel.VerticalAlignment.Center;             style.SetFont(font); IRow headRow sheet.GetRow(headTitleIndex);             int cols headRow.Cells.Count;             for (int colIndex 0; colIndex cols; colIndex)             {                 var cell headRow.GetCell(colIndex);                 cell.CellStyle style;             }         } /// summary         /// 设置合并单元格         /// /summary         /// param nameworkbook/param         /// param namesheetIndex/param         /// param namepicTitleRowIndex 内容行索引 /param         /// param namepicTitleColIndex 内容列索引/param         /// param nametitleInfo 显示信息/param         /// param namefirstRow/param         /// param namelastRow/param         /// param namefirstCol/param         /// param namelastCol/param         private void setMergeInfo(IWorkbook workbook, int sheetIndex, int picTitleRowIndex, int picTitleColIndex, string titleInfo,             int firstRow, int lastRow, int firstCol, int lastCol)         {             ISheet sheet workbook.GetSheetAt(sheetIndex);             CellRangeAddress cellRangeAddress new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);             sheet.AddMergedRegion(cellRangeAddress);             //sheet.GetRow(picTitleRowIndex).GetCell(picTitleColIndex).SetCellValue(titleInfo);             IRow row sheet.GetRow(picTitleRowIndex);             ICell cell row.CreateCell(picTitleColIndex); ///getCell(picTitleColIndex);失败需要create             cell.SetCellValue(titleInfo); ICellStyle style workbook.CreateCellStyle();             var font workbook.CreateFont();             font.FontHeightInPoints 14;             //font.FontName Arial;             font.FontName 仿宋;             font.Boldweight 800;             style.Alignment NPOI.SS.UserModel.HorizontalAlignment.Center;             style.VerticalAlignment NPOI.SS.UserModel.VerticalAlignment.Center;             style.SetFont(font);             cell.CellStyle style; } private void insertPic(IWorkbook workbook, ISheet sheet,string picpath,int rowleft,int rowright,int colleft,int colright)         {             //string picpath C:\Users\wangzg\Desktop\ceshi.png; //第一步 获取图片bytes字节             byte[] bytes File.ReadAllBytes(picpath); //第二步 确定图片索引注意图片类型             int ip workbook.AddPicture(bytes, PictureType.PNG);             //第三步 创建画布             IDrawing drawing sheet.CreateDrawingPatriarch();             //第三步 创建锚点             IClientAnchor anchor workbook.GetCreationHelper().CreateClientAnchor();             //第四步 设置锚点左上角  右下角 也就是图片的大小不过是通过左上点  和右下点来得到的             //anchor.Row1 2;             //anchor.Col1 1;             //anchor.Row2 12;             //anchor.Col2 12;             anchor.Row1 rowleft;             anchor.Col1 colleft;             anchor.Row2 rowright;             anchor.Col2 colright;             //第五步 把图片插入到相应位置             IPicture picture drawing.CreatePicture(anchor, ip); }     } private void button12_Click(object sender, EventArgs e)         {             try             {                 string[] headtitle { aaaa, bbb, ccc, ddd, eee };                 ListListstring lists new ListListstring();                 string target C:\Users\wangzg\Desktop\112_4.xls;                 IWorkbook workbook target.EndsWith(.xlsx) ? (IWorkbook)new XSSFWorkbook() : new HSSFWorkbook();                 ISheet sheet workbook.CreateSheet(taoya); int headTitleRowIndex 15; for (int i 0; i 15; i) {                     sheet.CreateRow(i);                 } //表头                 IRow headRow sheet.CreateRow(headTitleRowIndex);                 for (int i 0; i headtitle.Length; i)                 {                     headRow.CreateCell(i).SetCellValue(headtitle[i]);                 }                 //列值                 for (int i 0; i 10; i)                 {                     Liststring oil new Liststring();                     oil.Add(aa i);                     oil.Add(bb i);                     oil.Add(cc i);                     oil.Add(dd i);                     oil.Add(ee i);                     lists.Add(oil);                 } for (int i 0; i lists.Count; i)                 {                     IRow row sheet.CreateRow(sheet.LastRowNum 1);                     Liststring objs lists[i];                     for (int j 0; j headtitle.Length; j)                     {                         row.CreateCell(j).SetCellValue(objs[j]);                     }                 } setNormalCellStyle(workbook, 0);                 setHeadTitleStyle(workbook, 0, headTitleRowIndex);                 setMergeInfo(workbook, 0,1,1, aaaa);                 insertPic(workbook, workbook.GetSheetAt(0));                 using (var stream new FileStream(target, FileMode.Create, FileAccess.Write))                 {                     workbook.Write(stream);                 } MessageBox.Show(ok);             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         }