excl 到表工具

逻辑

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using System.IO;
using NPOI;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using NPOI.POIFS.FileSystem;

namespace WindowsFormsApp2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            label1.Text = "表路径";
            textBox1.Text = "F:\\excel_project\\StringConfig4.xlsx";
            button1.Text = "开始到表";
        }


        private void button1_Click(object sender, EventArgs e)
        {
            test();
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        #region
         void Create()
        {
            XSSFWorkbook wk = new XSSFWorkbook();
            //创建一个Sheet  
            ISheet sheet = wk.CreateSheet("例子");

            //在第一行创建行
            IRow row = sheet.CreateRow(0);
            //在第一行的第一列创建单元格
            for (int i = 0; i < 10; i++)
            {
                row.CreateCell(i).SetCellValue("测试");
            }


            using (FileStream fs = File.OpenWrite("F:\\excel_project\\pratice1.xlsx"))
            {
                wk.Write(fs);//向打开的这个xls文件中写入并保存。
            }
            Console.WriteLine("OK");
            Console.ReadKey();

        }

         void Write()
        {
            XSSFWorkbook wk = new XSSFWorkbook();
            创建一个Sheet  
            //ISheet sheet = wk.CreateSheet("例子");

            在第一行创建行
            //IRow row = sheet.CreateRow(0);
            在第一行的第一列创建单元格
            //for (int i = 0; i < 100; i++)
            //{
            //    ICell cell = row.CreateCell(i);
            //    if ((i + 1) % 4 == 0)
            //        row.CreateCell(i).SetCellValue("测试");
            //}

            string Address = "F:\\excel_project\\pratice1.xlsx"; //指明路径 
            using (FileStream fs = File.OpenWrite(Address))
            {
                wk.Write(fs);//向打开的这个xls文件中写入并保存。
            }


            //上一篇教程中生成的文件  
            XSSFWorkbook wk2 = null;
            using (FileStream fs = File.Open(Address, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                //把xlsx文件读入workbook变量里，之后就可以关闭了  
                wk2 = new XSSFWorkbook(fs);
                fs.Close();
            }

            using (FileStream fileStream = File.Open(Address, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                wk2.Write(fileStream);
                fileStream.Close();
            }

            Console.WriteLine("OK");
            Console.ReadKey();
        }

        #region npoi
         Dictionary<int, string> cellData = new Dictionary<int, string>();

         void test()
        {
            cellData.Clear();
            cellData.Add(0, "id");
            cellData.Add(1, textBox2.Text);
            cellData.Add(2, textBox3.Text);
            //addExcelData("F:\\excel_project\\StringConfig.xlsx", 1, cellData);
            addExcelData2(textBox1.Text, cellData);
        }

        /// <summary>
        /// 向已存在的excel追加数据
        /// </summary>
        /// <param name="excelPath">已存在的excel路径</param>
        /// <param name="rowIndex">追加行索引</param>
        /// <param name="cellData">追加列索引<列索引，单元格值></param>
        public  void addExcelData2(string excelPath, Dictionary<int, string> cellData)
        {
            //读取文件
            XSSFWorkbook workbook;
            ICell cell = null;
            ICellStyle style = null;

            using (FileStream fs = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                workbook = new XSSFWorkbook(fs);
                fs.Close();
            }

            //获取工作表
            if (workbook.NumberOfSheets <= 0)
            {
                workbook.CreateSheet();
            }
            ISheet sheet = workbook.GetSheetAt(0);

            int rowIndex = sheet.LastRowNum;
            int tempId = int.Parse(sheet.GetRow(rowIndex).GetCell(0).ToString());

            string str = "表命：" + sheet.SheetName
               + "\n" + "最大行数:  " + sheet.LastRowNum
                + "\n" + "最大列数:  " + sheet.GetRow(0).LastCellNum
                + "\n" + "上一行的id:  " + tempId
                + "\n" + "上一行的注释:  " + sheet.GetRow(rowIndex).GetCell(1).ToString()
                + "\n" + "上一行的中文:  " + sheet.GetRow(rowIndex).GetCell(2).ToString();

            tempId++;
            rowIndex++;


            string str2 = "\n" + "新的id:  " + tempId
              + "\n" + "新的注释:  " + cellData[1]
              + "\n" + "新的中文:  " + cellData[2];

            richTextBox1.Text = str+ str2;


            获取行
            IRow row = sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = sheet.CreateRow(rowIndex);
            }

            foreach (KeyValuePair<int, string> keyValue in cellData)
            {
                cell = row.GetCell(keyValue.Key);
                if (cell == null)
                {
                    cell = row.CreateCell(keyValue.Key);
                }

                if (keyValue.Key == 0)
                {
                    cell.SetCellValue(tempId);
                }
                else
                {
                    cell.SetCellValue(keyValue.Value);
                }

                //设置居中
                style = workbook.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                cell.CellStyle = style;
            }


            //using (FileStream fs = File.OpenWrite("F:\\excel_project\\StringConfig1.xlsx"))
            //{
            //    fs.Flush();
            //    workbook.Write(fs);
            //}


            using (FileStream fileStream = File.Open(excelPath, FileMode.Open, FileAccess.ReadWrite))
            {
                fileStream.Flush();
                workbook.Write(fileStream);
                fileStream.Close();
            }


        }


        //for (int r = 0; r < 10; r++) {
        //    row= sheet.GetRow(r);
        //    for (int l = 0; l < 3; l++) {
        //        cell = row.GetCell(l);
        //        if (cell != null)
        //        {
        //            Console.WriteLine(cell.ToString());
        //        }
        //    }
        //}
        #endregion

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = dialog.FileName;
            }
        }
    }
    #endregion

}

界面

using System;
using System.Windows.Forms;

namespace WindowsFormsApp2
{
    partial class Form1
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows 窗体设计器生成的代码

        /// <summary>
        /// 设计器支持所需的方法 - 不要修改
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.button1 = new System.Windows.Forms.Button();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.richTextBox1 = new System.Windows.Forms.RichTextBox();
            this.textBox2 = new System.Windows.Forms.TextBox();
            this.textBox3 = new System.Windows.Forms.TextBox();
            this.label2 = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this.button2 = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(42, 142);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(529, 32);
            this.button1.TabIndex = 0;
            this.button1.Text = "开始到表";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // textBox1
            // 
            this.textBox1.Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.textBox1.Location = new System.Drawing.Point(120, 12);
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(359, 32);
            this.textBox1.TabIndex = 1;
            this.textBox1.TextChanged += new System.EventHandler(this.textBox1_TextChanged);
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.label1.Location = new System.Drawing.Point(38, 15);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(76, 22);
            this.label1.TabIndex = 2;
            this.label1.Text = "表路径";
            this.label1.Click += new System.EventHandler(this.label1_Click);
            // 
            // richTextBox1
            // 
            this.richTextBox1.Location = new System.Drawing.Point(42, 201);
            this.richTextBox1.Name = "richTextBox1";
            this.richTextBox1.Size = new System.Drawing.Size(529, 169);
            this.richTextBox1.TabIndex = 6;
            this.richTextBox1.Text = "";
            // 
            // textBox2
            // 
            this.textBox2.Location = new System.Drawing.Point(120, 65);
            this.textBox2.Name = "textBox2";
            this.textBox2.Size = new System.Drawing.Size(451, 21);
            this.textBox2.TabIndex = 7;
            // 
            // textBox3
            // 
            this.textBox3.Location = new System.Drawing.Point(120, 105);
            this.textBox3.Name = "textBox3";
            this.textBox3.Size = new System.Drawing.Size(451, 21);
            this.textBox3.TabIndex = 8;
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.label2.Location = new System.Drawing.Point(44, 68);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(54, 22);
            this.label2.TabIndex = 9;
            this.label2.Text = "注释";
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.label3.Location = new System.Drawing.Point(44, 105);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(54, 22);
            this.label3.TabIndex = 10;
            this.label3.Text = "中文";
            // 
            // button2
            // 
            this.button2.Location = new System.Drawing.Point(485, 12);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(86, 32);
            this.button2.TabIndex = 11;
            this.button2.Text = "选择";
            this.button2.UseVisualStyleBackColor = true;
            this.button2.Click += new System.EventHandler(this.button2_Click);
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(600, 415);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.textBox3);
            this.Controls.Add(this.textBox2);
            this.Controls.Add(this.richTextBox1);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.button1);
            this.Name = "Form1";
            this.Text = "自动插入数据";
            this.Load += new System.EventHandler(this.Form1_Load);
            this.ResumeLayout(false);
            this.PerformLayout();

        }


        private void label1_Click(object sender, EventArgs e)
        {

        }


        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            
        }

        #endregion

        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.RichTextBox richTextBox1;
        private System.Windows.Forms.TextBox textBox2;
        private System.Windows.Forms.TextBox textBox3;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.Label label3;
        private Button button2;
    }
}