NPOI读写excel

项目场景：

目标电脑没有EXCEL，但是保存数据要按照excel的模板保存。

---

问题描述：

由于没有office，所以开发不能基于office组件开发，在网上找了很多列子，最后基于开源的NPOI来开发。

首先要引用以下几个dll

2.上写入EXCEL代码，传入的数据“dt”可以是数组或者其他格式，稍加改变就可以。

        /// <summary>
        /// datatable write to excel
        /// </summary>
        /// <param name="dt">需要写入的数据，datatable类型</param>
        /// <param name="partten_file">需要写入的Excel模板路径</param>
        /// <param name="save_path">需要保存的Excel模板路径</param>
        /// <param name="WriteHeader">是否写入表头</param>
        /// <param name="Col">列起始坐标</param>
        /// <param name="Row">行起始坐标</param>
        /// <param name="replace_table">是否覆盖</param>
        public void TableToExcel(DataTable dt, string partten_file, string save_path,bool WriteHeader, int Col, int Row,bool replace_table)
        {
            IWorkbook workbook = null;
            string fileExt = Path.GetExtension(partten_file).ToLower();
            if (fileExt == ".xlsx")
            {
                using (FileStream fs = new FileStream(partten_file, FileMode.Open, FileAccess.Read))
                {
                    InputStream inp = new FileInputStream(fs);
                    workbook = new XSSFWorkbook();
                    fs.Close();
                }
            }
            else if (fileExt == ".xls")
            {
                using (FileStream fs = new FileStream(partten_file, FileMode.Open, FileAccess.Read))
                {
                    workbook = new HSSFWorkbook(fs);
                    fs.Close();
                }
            }
            else { workbook = null; }
            if (workbook == null)
            {
                return;
            }
            ISheet sheet = workbook.CreateSheet();
            //写入表头  
            if (WriteHeader == true)
            {
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }
            }
            //是否覆盖table所有的数据  TRUE REPLACE
            if (replace_table == true)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1 + Row);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j + Col);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
            }
            else
            {
                //模板写入带完善
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = (ICell)sheet.GetRow(i).GetCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());                      
                    }
                }
            }
            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            //保存为Excel文件  
            using (FileStream fs = new FileStream(save_path, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
                fs.Close();
            }
        }

3.EXCEL导入代码，返回的数据“dt”可以转换为二维数组。

---

      /// <summary>
        /// Excel导入Datable，返回datatable格式数据。也可以自行转换为二维数据
        /// </summary>
        /// <param name="file">存储excle的路径</param>
        /// <returns></returns>
        public DataTable ExcelToTable(string file)
        {
            cLogger.Instance.WriteLog(file);
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileExt == ".xls")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return null;
                }
                ISheet sheet = workbook.GetSheetAt(0);
                //写入table header
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum;
                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue = cell.StringCellValue;
                        if (!string.IsNullOrEmpty(cellValue))
                        {
                            //设置DataTable的第一行的显示内容即标题行
                            dt.Columns.Add(cellValue);
                        }
                    }
                }
                //写入数据
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    IRow row = sheet.GetRow(i);  //读取当前行数据
                    if (row != null)
                    {
                        //LastCellNum 是当前行的总列数
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //读取该行的第j列数据
                            string value = row.GetCell(j).ToString();
                            dr[j] = value;

                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

---

4.使用NPOI向Excel单元格中插入图片,图片是bitmap类型。

调用demo向0列1行插入一张图片
InsertPicture((Bitmap)Bitmap.FromFile("图片1.png"), "TestExcel_5.xlsx", 0, 1);




/// <summary>
        /// 将BitMap转换成bytes数组
        /// </summary>
        /// <param name="bitmap">要转换的图像</param>
        /// <returns></returns>
        private static byte[] BitmapToByte(System.Drawing.Bitmap bitmap)
        {
            // 1.先将BitMap转成内存流
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            bitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            // 2.再将内存流转成byte[]并返回
            byte[] bytes = new byte[ms.Length];
            ms.Read(bytes, 0, bytes.Length);
            ms.Dispose();
            return bytes;
        }
        private static void InsertPicture(Bitmap bitmap, string path,int colindex, int rowindex)
        {
            // 图片宽高
            int imgWid = bitmap.Width;
            int imgHgt = bitmap.Height;
            // 打开Excel
            IWorkbook excel = default;
            ISheet sheet = default;
            byte[] buffer = BitmapToByte(bitmap);
            if (!File.Exists(path))
            {
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Restart();
                excel = new XSSFWorkbook();             
                // 获取第一个表格
                sheet = excel.CreateSheet("sheet1");                

                // 获取第二行，第一行是标题
                IRow row = sheet.CreateRow(rowindex);
                // 为第一列设置文本
                //row.CreateCell(0).SetCellValue("测试标题");
                // 设置行高和列宽
                sheet.SetColumnWidth(colindex, imgWid * 32);   // Excel的宽将1个像素分为32份
                row.Height = (short)(imgHgt * 16);      // Excel的高将1个像素分为16份

                // 添加图片
                // 1.以字节数组的形式读取图片
                //byte[] buffer = GetImageBuffer(path);
                // 2.向Excel添加图片，获取到图片索引
                int picIdx = excel.AddPicture(buffer, PictureType.JPEG);
                // 3.构建Excel图像
                var drawing = sheet.CreateDrawingPatriarch();
                // 4.确定图像的位置
                // new XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
                // 前四个参数是单元格内的偏移量，这里需要填满整个单元格所以不设置。
                // col1,row1表示图片的左上角在哪个单元格的左上角；col2,row2表示图片的右下角在哪个单元格的左上角
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, colindex, rowindex, colindex+1, rowindex+1);

                //XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 2, 1, 3);
                // 5.将图片设置到上面的位置中
                drawing.CreatePicture(anchor, picIdx);

                using (FileStream newFile = new FileStream(path, FileMode.Create, FileAccess.Write))
                {
                    excel.Write(newFile);
                }
                stopwatch.Stop();
                double ff = stopwatch.ElapsedMilliseconds;
            }
            else
            {
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Restart();
                using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Read))
                {
                    //excel = new XSSFWorkbook();
                    excel = WorkbookFactory.Create(fs);
                    // 获取第一个表格
                    //sheet = excel.CreateSheet("sheet1");
                    sheet = excel.GetSheetAt(0);

                    // 获取第二行，第一行是标题
                    IRow row = sheet.CreateRow(rowindex);
                    // 为第一列设置文本
                    //row.CreateCell(0).SetCellValue("测试标题");
                    // 设置行高和列宽
                    sheet.SetColumnWidth(colindex, imgWid * 32);   // Excel的宽将1个像素分为32份
                    row.Height = (short)(imgHgt * 16);      // Excel的高将1个像素分为16份

                    // 添加图片
                    // 1.以字节数组的形式读取图片
                  
                    // 2.向Excel添加图片，获取到图片索引
                    int picIdx = excel.AddPicture(buffer, PictureType.JPEG);
                    // 3.构建Excel图像
                    var drawing = sheet.CreateDrawingPatriarch();
                    // 4.确定图像的位置
                    // new XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
                    // 前四个参数是单元格内的偏移量，这里需要填满整个单元格所以不设置。
                    // col1,row1表示图片的左上角在哪个单元格的左上角；col2,row2表示图片的右下角在哪个单元格的左上角
                    //XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 2, 1, 3);
                    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, colindex, rowindex, colindex + 1, rowindex + 1);
                    // 5.将图片设置到上面的位置中
                    drawing.CreatePicture(anchor, picIdx);

                }
                using (FileStream newFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    excel.Write(newFile);
                }
                stopwatch.Stop();
                double ff = stopwatch.ElapsedMilliseconds;
            }
            excel?.Close();
        }

---

5.datatable和二维数组互相转换。

---

     /// <summary>
        /// datatable to array
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string[,] DataTableToArray(DataTable dt)
        {
            if (dt == null)
                return  null;
            int i = 0;
            int rowsCount = dt.Rows.Count;
            int colsCount = dt.Columns.Count;
            string[,] arrReturn = new string[rowsCount, colsCount];           
            foreach (System.Data.DataRow row in dt.Rows)
            {
                int j = 0;
                foreach (System.Data.DataColumn column in dt.Columns)
                {
                    arrReturn[i, j] = row[column.ColumnName].ToString();
                    j = j + 1;
                }
                i = i + 1;
            }
            return arrReturn;
        }

        /// <summary>
        /// array to datatable
        /// </summary>
        /// <param name="arr"></param>
        /// <returns></returns>
        public DataTable ArrayToDataTable(string[,] arr)
        {

            DataTable dataSouce = new DataTable();
            for (int i = 0; i < arr.GetLength(1); i++)
            {
                DataColumn newColumn = new DataColumn(i.ToString(), arr[0, 0].GetType());
                dataSouce.Columns.Add(newColumn);
            }
            for (int i = 0; i < arr.GetLength(0); i++)
            {
                DataRow newRow = dataSouce.NewRow();
                for (int j = 0; j < arr.GetLength(1); j++)
                {
                    newRow[j.ToString()] = arr[i, j];
                }
                dataSouce.Rows.Add(newRow);
            }
            return dataSouce;
        } 

---

以上代码都是经过生产测试过，各位看官拿去就可以用，如果有好的idea欢迎一起分享学习。模板写入数据没有连着的需要多次调用写入操作，也可以自行更改