广州知名网站建设哪家公司好网站案例分析
- 今天在弄使用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.BoldWeight=800来设置，注意是对每一个cell来设置样式 cell.cellStyle= style;也就是for循环行和列来设置样式，
- 合并单元格
只需要new CellRangeAddress()即可，然后sheet.addMergendRegion(cellRangeAddress),这样就合并了，但是合并后，需要写入信息，以及重新设置样式
public class WZExcelUtil
{
/// <summary>
/// 设置一般表格样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetIndex"></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 name="workbook"></param>
/// <param name="sheetIndex"></param>
/// <param name="headTitleIndex"></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 name="workbook"></param>
/// <param name="sheetIndex"></param>
/// <param name="picTitleRowIndex"> 内容行索引 </param>
/// <param name="picTitleColIndex"> 内容列索引</param>
/// <param name="titleInfo"> 显示信息</param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="firstCol"></param>
/// <param name="lastCol"></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" };
List<List<string>> lists = new List<List<string>>();
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++)
{
List<string> oil = new List<string>();
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);
List<string> 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);
}
}