逻辑
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;
}
}