NPOI初级教程

NPOI

https://archive.codeplex.com/?p=npoi

NPOI下载官网http://npoi.codeplex.com

下载解压，里面有个dotnet4的文件夹，把它拖到自己的项目中，把里面的.dll全部添加引用

---

NPOI如何向已存在的Excel文件中插入一行数据

---

基础概念

excel2007：xlsx

XSSFWorkbook wb;
XSSFSheet sh;

excel2003 ：xls

HSSFWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet("Images");

---

IRow dataRow = confidence_sheet.CreateRow(i);
ICell  cell = dataRow.CreateCell(j);

ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;

cell.CellStyle = cellstyle;

读取现有的Excel

HSSFWorkbook wb;
FileStream file;
file = new FileStream(filepath, FileMode.Open, FileAccess.Read);
wb = new HSSFWorkbook(file);
file.Close();
// 获取已有表
ISheet image_sheet = wk.GetSheet("Images");
ISheet confidence_sheet = wk.GetSheet("Confidence");
int rowsCount = image_sheet .PhysicalNumberOfRows; //取行Excel的最大行数
int colsCount = image_sheet .GetRow(0).PhysicalNumberOfCells;//取得Excel的列数

写excel

var workBook = new HSSFWorkbook();
ISheet sheet = null;

sheet = workBook.CreateSheet("sheet1");//创建表格

IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("姓名");
workBook.SetActiveSheet(0);//设置默认表格
string filePath=@"test.xls";
using (FileStream fs = new FileStream(filePath, FileMode.Create))
{
    workBook.Write(fs);
}            

---

https://blog.csdn.net/mouday/article/details/81049219

---

C# 使用NPOI操作Excel文件

---

c#开源Excel操作库–NPOI

---

使用Npoi向Excel中插入图片

---

https://www.cnblogs.com/wei325/p/4748324.html

---

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (FileStream fs = File.OpenRead(@"F:\\PycharmWorkPlace\\test2excel\\test.xlsx"))   //打开myxls.xls文件{
            {
                
                XSSFWorkbook wb = new XSSFWorkbook(fs);
               // HSSFWorkbook wk = new HSSFWorkbook(fs);
                ISheet sh = wb.GetSheet("Sheet1");
                Console.WriteLine(sh.GetRow(0).GetCell(0).StringCellValue);
                Console.ReadLine();




            }

        }
    }
}

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.CreateSheet( "Sheet1") as HSSFSheet;

IRow row = sheet.CreateRow(0);

    row.Height = 30 * 20;



ICell cellTitle = row.CreateCell(0);

    titleHeader.Alignment = HorizontalAlignment.Center;

    titleHeader.VerticalAlignment = VerticalAlignment.Center;

    

        style.BorderBottom = BorderStyle.Thin;

        style.BorderLeft = BorderStyle.Thin;

        style.BorderRight = BorderStyle.Thin;



IFont font = workbook.CreateFont();

    font.FontHeightInPoints = 14;

    font.FontName = "微软雅黑";

        font.IsBold = true;



cellTitle.SetFont(font);

cellTitle.SetCellValue(titleName)




Color c = Color.FromArgb(215, 228, 188);

                HSSFPalette palette = workbook.GetCustomPalette();

                palette.SetColorAtIndex((short)63, c.R, c.G, c.B);

                HSSFColor cellColor = palette.FindColor(c.R, c.G, c.B);

style.FillPattern = FillPattern.SolidForeground;

style.FillForegroundColor = cellColor.Indexed;



region = new CellRangeAddress(3, 3, 15, columnsCount - 1);

sheet.AddMergedRegion(region);

((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, HSSFColor.Black.Index);





   //列宽自适应，只对英文和数字有效

                for (int i = 0; i <= columnsCount; i++)

                {

                    sheet.AutoSizeColumn(i);

                }



                //列宽自适应中文有效

                for (int i = 0; i < 15; i++)

                {

                    int columnWidth = sheet.GetColumnWidth(i) / 256;

                    for (int rowNum = 4; rowNum < 6 + rowsCount; rowNum++)

                    {

                        IRow currentRow;

                        //当前行未被使用过

                        if (sheet.GetRow(rowNum) == null)

                        {

                            currentRow = sheet.CreateRow(rowNum);

                        }

                        else

                        {

                            currentRow = sheet.GetRow(rowNum);

                        }



                        if (currentRow.GetCell(i) != null)

                        {

                            ICell currentCell = currentRow.GetCell(i);

                            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;

                            if (columnWidth < length)

                            {

                                columnWidth = length;

                            }

                        }

                    }

                    sheet.SetColumnWidth(i, columnWidth * 350);

                }



                //列宽自适应中文有效

                for (int i = 15; i < columnsCount; i++)

                {

                    int rowNum;



                    if (dtSource.Columns[i].ColumnName.Contains("/"))

                    {

                        rowNum = 4;

                    }

                    else

                    {

                        rowNum = 5;

                    }



                    int columnWidth = sheet.GetColumnWidth(i) / 256;

                    for (; rowNum < 6 + rowsCount; rowNum++)

                    {

                        IRow currentRow;

                        //当前行未被使用过

                        if (sheet.GetRow(rowNum) == null)

                        {

                            currentRow = sheet.CreateRow(rowNum);

                        }

                        else

                        {

                            currentRow = sheet.GetRow(rowNum);

                        }



                        if (currentRow.GetCell(i) != null)

                        {

                            ICell currentCell = currentRow.GetCell(i);

                            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;

                            if (columnWidth < length)

                            {

                                columnWidth = length;

                            }

                        }

                    }

                    sheet.SetColumnWidth(i, columnWidth * 350);

                }




 //若没有数据则建立空文档

            if (workbook.NumberOfSheets == 0)

            {

                HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;

            }



  //写文件

            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);

            ms.Flush();

            ms.Seek(0, SeekOrigin.Begin); //ms.Position = 0;



    return ms;



   if (j == 14 )

    {

    double db = 0;

    if (double.TryParse(objVal.ToString(), out db))

    {

        cell.SetCellValue(db);

    }

    }

    else

    {

    SetCellValue(cell, objVal);

    }



public static void SetCellValue(ICell eCell, object data)

{

    string typeStr = data.GetType().ToString();



    switch (typeStr)

    {

        case "System.String":

            eCell.SetCellValue(data.ToString());

            break;

        case "System.DateTime":

            System.DateTime dateV;

            System.DateTime.TryParse(data.ToString(), out dateV);

            eCell.SetCellValue(dateV.ToString("yyyy/MM/dd"));

            break;

        case "System.Boolean":

            bool boolV = false;

            bool.TryParse(data.ToString(), out boolV);

            eCell.SetCellValue(boolV);

            break;

        case "System.Int16":

        case "System.Int32":

        case "System.Int64":

        case "System.Byte":

            int intV = 0;

            int.TryParse(data.ToString(), out intV);

            eCell.SetCellValue(intV);

            break;

        case "System.Decimal":

        case "System.Double":

            double doubV = 0;

            double.TryParse(data.ToString(), out doubV);

            eCell.SetCellValue(doubV);

            break;

        case "System.DBNull":

            eCell.SetCellValue("");

            break;

        default:

            eCell.SetCellValue("");

            break;

    }

}