c# npoi操作excel

server/2024/11/19 7:02:00/
  1. 今天在弄使用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) {}

        }

  1. 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的

  1. Cell样式

注意style通过workbook.createStyle(),而不是通过其他获得

有的api有font.isBold =true;但是我这个没有只有通过font.BoldWeight=800来设置,注意是对每一个cell来设置样式 cell.cellStyle= style;也就是for循环行和列来设置样式,

  1. 合并单元格

只需要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);
            }
        }


http://www.ppmy.cn/server/143104.html

相关文章

工具类-基于 axios 的 http 请求工具 Request

基于 axios 的 http 请求工具 基于 axios 实现一个 http 请求工具&#xff0c;支持设置请求缓存和取消 http 请求等功能 首先实现一个 简单的 http 请求工具 import axios, {AxiosError,AxiosInterceptorManager,AxiosRequestConfig,AxiosResponse, } from axios;// 接口返回…

华为刷题笔记--题目索引

文章目录 更多关于刷题的内容欢迎订阅我的专栏华为刷题笔记简单题目 更多关于刷题的内容欢迎订阅我的专栏华为刷题笔记 该专栏题目包含两部分&#xff1a; 100 分值部分题目 200 分值部分题目 所有题目都会陆续更新&#xff0c;订阅防丢失 简单题目 –题目分值试卷1华为OD机…

数据科学与SQL:如何计算排列熵?| 基于SQL实现

目录 0 引言 1 排列熵的计算原理 2 数据准备 3 问题分析 4 小结 0 引言 把“熵”应用在系统论中的信息管理方法称为熵方法。熵越大&#xff0c;说明系统越混乱&#xff0c;携带的信息越少&#xff1b;熵越小&#xff0c;说明系统越有序&#xff0c;携带的信息越多。在传感…

【Unity基础】对比Unity中碰撞类与触发类交互机制

碰撞类回调&#xff08;Collision-based&#xff09;和触发类回调&#xff08;Trigger-based&#xff09;是Unity中两种不同的物理交互机制。它们各自有不同的应用场景和使用方式&#xff0c;理解它们的区别有助于我们在游戏开发中选择合适的解决方案。下面是这两者的详细对比&…

【算法】【优选算法】前缀和(下)

目录 一、560.和为K的⼦数组1.1 前缀和1.2 暴力枚举 二、974.和可被K整除的⼦数组2.1 前缀和2.2 暴力枚举 三、525.连续数组3.1 前缀和3.2 暴力枚举 四、1314.矩阵区域和4.1 前缀和4.2 暴力枚举 一、560.和为K的⼦数组 题目链接&#xff1a;560.和为K的⼦数组 题目描述&#x…

网页web无插件播放器EasyPlayer.js H.265流媒体播放器的decoder.js报Unexpected token ‘<‘错误

EasyPlayer.js H.265流媒体播放器属于一款高效、精炼、稳定且免费的流媒体播放器&#xff0c;可支持多种流媒体协议播放&#xff0c;支持H.264与H.265编码格式&#xff0c;性能稳定、播放流畅&#xff1b;支持WebSocket-FLV、HTTP-FLV&#xff0c;HLS&#xff08;m3u8&#xff…

【android USB 串口通信助手】stm32 源码demo 单片机与手机通信 Android studio 20241118

android 【OTG线】 接 下位机STM32【USB】 通过百度网盘分享的文件&#xff1a;USBToSerialPort.apk 链接&#xff1a;https://pan.baidu.com/s/122McdmBDUxEtYiEKFunFUg?pwd8888 提取码&#xff1a;8888 android 【OTG线】 接 【USB转TTL】 接 【串口(下位机 SMT32等)】 需…

c++:模板

1.泛型编程 在认识模板之前&#xff0c;我们首先要认识泛型编程 泛型编程是一种编程范式&#xff0c;它使得算法和数据结构能够独立于特定数据类型进行设计和实现。通过使用泛型&#xff0c;开发者可以编写一次代码&#xff0c;然后在不同的数据类型上进行重用&#xff0c;从…