我的c#程序--excel read and write

最新推荐文章于 2024-06-26 08:00:00 发布

Gao Jian709

最新推荐文章于 2024-06-26 08:00:00 发布

阅读量324

点赞数

文章标签： c#

本文链接：https://blog.youkuaiyun.com/weixin_59564970/article/details/125356178

版权

该博客介绍了如何使用C#通过NPOI库读取和写入Excel文件。程序首先读取Excel文件中的数据，将其存储在DataTable对象中，然后将DataTable的内容写入新的Excel文件。主要涉及了对Excel工作簿、工作表、行和单元格的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录亲自写的一个小程序

主要功能，读取excel中内容，然后根据excel中的内容写入到新的excel中

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
class MyClass
{
    static void Main(string[] args)
    {
        MyClass myClass = new MyClass();
        DataTable dataTable = myClass.ReadData();
        myClass.WriteData(dataTable);

    }
    /// <summary>
    /// 读取excel中的数据
    /// </summary>
    /// <returns>返回datatable数据</returns>
    public DataTable ReadData()
    {
        //实例化一个datatable用来存储数据
        DataTable dt = new DataTable();
       
        //指定excel所在路径
        string excelPath = "d:\\1234.xlsx";

        //以文件流读取excel文件
        FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read);
        //Offic2007以上
        IWorkbook workbook = new XSSFWorkbook(fs);

        //取第一个sheet页的数据
        ISheet sheet = workbook.GetSheetAt(0);

        //获取第一行数据
        IRow firstRow = sheet.GetRow(0);
        //获取第一行中数据列的数量
        int columnCount = firstRow.LastCellNum;
        for (int i = firstRow.FirstCellNum; i <= columnCount; i++)
        {
            //获取单元格
            ICell cell = firstRow.GetCell(i);
            if (cell != null)
            {
                //将第一行数据作为表头
                string cellValue = cell.StringCellValue;
                if (!string.IsNullOrEmpty(cellValue))
                {
                    dt.Columns.Add(cellValue);
                }
            }
        }
        //获取数据行的数量
        int rowCount = sheet.LastRowNum;
        for (int i = 1; i <= rowCount; i++)
        {
            //获取行
            IRow row = sheet.GetRow(i);
            if (row == null) continue;
            DataRow ?dataRow = dt.NewRow();
            for (int j = row.FirstCellNum; j <= dt.Columns.Count; j++)
            {
                if (row.GetCell(j) != null)
                {
                    //这里减FirstCellNum可以去除空白列数据
                    dataRow[j - row.FirstCellNum] = row.GetCell(j).ToString();
                }
            }
            dt.Rows.Add(dataRow);
        }
        //资源释放
        workbook.Close();
        return dt;
    }
    /// <summary>
    /// 往excel中写入数据
    /// </summary>
    /// <param name="dt">dt中存放这读取到的excel数据</param>
    public void WriteData(DataTable dt)
    {
        int rowConnt = dt.Rows.Count;
        int colCount = dt.Columns.Count;

        //1、新建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //2、新建一个sheet表
        workbook.CreateSheet("sheet1");

        //3、获取名称为sheet1的工作表
        HSSFSheet sheet1 = (HSSFSheet)workbook.GetSheet("sheet1");

        //4、往sheet1里面插入数据       
        int currentLine = 0;
        HSSFCell[] SheetCell = new HSSFCell[colCount];
        HSSFRow SheetRow;
        FileStream file2003 = new FileStream(@"E:\Excel999.xls", FileMode.Create);
        for (int i = 0; i < rowConnt; i++)
        {
            DataRow dr = dt.Rows[i];
            sheet1.CreateRow(currentLine );
            SheetRow = (HSSFRow)sheet1.GetRow(currentLine);  //获取Sheet1工作表的当前行  
            for (int j = 0; j < colCount; j++)
            {

                SheetCell[j] = (HSSFCell)SheetRow.CreateCell(j);
                string? colString = dr[j].ToString();
                if (j == 9)
                {
                    int itemCount = colString != null ? colString.Split(@"D:\").Length : 0;
                    if (itemCount <= 2)
                    {
                        SheetCell[j].SetCellValue(colString);
                        if (colString.Contains(@"\"))
                        {
                            string[] hang = colString.Split(@"\");
                            string strings = hang[hang.Length - 1];
                            SheetCell[6].SetCellValue(strings);
                        }
                        else if (colString.Contains(@"/"))
                        {
                            string[] hang = colString.Split(@"/");
                            string strings = hang[hang.Length - 1];
                            SheetCell[6].SetCellValue(strings);
                        }
                        else
                        {
                            SheetCell[6].SetCellValue("");
                        }
                    }
                    else
                    {
                        string[] strings = colString.Split(@"D:\");
                        for (int z = 0; z < itemCount - 1; z++)
                        {
                            currentLine = currentLine + z;
                            sheet1.CreateRow(currentLine);
                            SheetRow = (HSSFRow)sheet1.GetRow(currentLine);  //获取Sheet1工作表的当前行                            
                            for (int v = 0; v < colCount; v++)
                            {
                                SheetCell[v] = (HSSFCell)SheetRow.CreateCell(v);
                                string? colString2 = dr[v].ToString();
                                if (v == 9)
                                {
                                    if (strings[z + 1].Contains(@"\"))
                                    {
                                        string[] hang = strings[z + 1].Split(@"\");
                                        string strings2 = hang[hang.Length - 1];
                                        SheetCell[6].SetCellValue(strings2);
                                        string string3 = "D:\\" + strings[z + 1];
                                        SheetCell[v].SetCellValue(string3);
                                    }
                                    else if (strings[z + 1].Contains(@"/"))
                                    {
                                        string[] hang = strings[z + 1].Split(@"/");
                                        string strings2 = hang[hang.Length - 1];
                                        SheetCell[6].SetCellValue(strings2);
                                        string string3 = "D://" + strings[z + 1];
                                        SheetCell[v].SetCellValue(string3);
                                    }
                                    else
                                    {
                                        SheetCell[6].SetCellValue("");
                                    }
                                }
                                else 
                                {
                                    SheetCell[v].SetCellValue(colString2);
                                }
                                
                            }
                        }
                    }
                }
                else
                {
                    SheetCell[j].SetCellValue(colString);
                }                
            }
            currentLine++;
        }       
        workbook.Write(file2003);
        file2003.Close();
        workbook.Close();
    }
}