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

c# npoi操作excel

article 2024/11/22 22:35:40

今天在弄使用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);

}

        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);
            }
        }

查看全文

http://www.kler.cn/a/404907.html