账单1.4 ：NPOI +Excel Winform版

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 NPOI;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Formula.Functions;
using ICSharpCode.SharpZipLib;

namespace 账单NPOI_Excel_WinForm
{
    public partial class Form1 : Form
    {
        int lastrow;
        int lastcolumn;
        string path = @"J:\a.xls";
        HSSFWorkbook workbook;

        public Form1()
        {
            InitializeComponent();

            //初始化
            using (FileStream fs = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))

            {
                workbook = new HSSFWorkbook(fs);//初始化时，即建立workbook.
                fs.Close();
            }
            ISheet sheet2 = workbook.GetSheet("list");//在listBOX中填加内容
            for (int i = 0; i < sheet2.LastRowNum + 1; i++)
            {
                IRow row = sheet2.GetRow(i);
                ICell cell = row.GetCell(0);
                listBox1.Items.Add(cell.ToString());
                listBox2.Items.Add(cell.ToString());

            }
            //以下为读取当前的金额数值

            ISheet sheet = workbook.GetSheet("main");
            IRow moneyrow = sheet.GetRow(1);
            IRow titlerow = sheet.GetRow(0);

            for (int i = 1; i <= 10; i++)
            {
                tx1 = (TextBox)this.Controls.Find("textBox" + i.ToString(), true)[0];
                //tx2 = (TextBox)this.Controls.Find("textBox" + (i+10).ToString(), true)[0];
                lab1 = (Label)this.Controls.Find("label" + i.ToString(), true)[0];
              
                ICell cell = moneyrow.GetCell(i);
                tx1.Text = cell.ToString();
                ICell cell1 = titlerow.GetCell(i);
                lab1.Text = cell1.ToString();
            }

            double total=0;
            for (int i = 1; i < 10; i++)
            {
                tx1 = (TextBox)this.Controls.Find("textBox" + i.ToString(), true)[0];
                total = total + double.Parse(tx1.Text);

            }

            label11.Text = "总金额:" + total.ToString();
        }

        TextBox tx1;
        TextBox tx2;
        Label lab1;
        private void button1_Click(object sender, EventArgs e)
        {
            ISheet sheet3 = workbook.GetSheet("main");
           
            int lastrow = sheet3.LastRowNum;
            int box1 = listBox1.SelectedIndex;
            int box2 = listBox2.SelectedIndex;
           // MessageBox.Show("listbox1 index is"+box1+ "listbox2 is "+box2);

            ICell cell2 = sheet3.CreateRow(lastrow + 1).CreateCell(box2 + 1);//末行对应栏显示正值表示移入
            cell2.SetCellValue(double.Parse(textBox11.Text));

            ICell cell1 = sheet3.GetRow(lastrow + 1).CreateCell(box1 + 1);//末行显示负值，表示移出
            cell1.SetCellValue( - double.Parse(textBox11.Text));
    

            ICell cell5= sheet3.GetRow(lastrow + 1).CreateCell(0); //左列显示日期
            cell5.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));

            ICell cell3 =sheet3.GetRow(1).GetCell(box1+1);//总数值扣除相应金额，代表移出对应box1的index
            string chushi = cell3.ToString();//获取对应的数值
            double cell3newvalue = double.Parse(chushi) - double.Parse(textBox11.Text);//计算出新的数值
            cell3.SetCellValue(cell3newvalue);//再次赋值给这个cell

            ICell cell4 = sheet3.GetRow(1).GetCell(box2+1);//加上plus相应金额,对应box2的index
            string chushi1 = cell4.ToString();
            double cell4newvalue = double.Parse(chushi1) + double.Parse(textBox11.Text);
            cell4.SetCellValue(cell4newvalue);

            using (FileStream fs = File.OpenWrite(path))
            {
                workbook.Write(fs);//向打开的这个xls文件中写入并保存。
            }
            // MessageBox.Show("run successfully");

 

 

           // 以下为完成记录之后，再更新一次Form中的数值

            ISheet sheet = workbook.GetSheet("main");
            IRow moneyrow = sheet.GetRow(1);
            IRow titlerow = sheet.GetRow(0);

            for (int i = 1; i <= 10; i++)
            {
                tx1 = (TextBox)this.Controls.Find("textBox" + i.ToString(), true)[0];
                //tx2 = (TextBox)this.Controls.Find("textBox" + (i+10).ToString(), true)[0];
                lab1 = (Label)this.Controls.Find("label" + i.ToString(), true)[0];

                ICell cell = moneyrow.GetCell(i);
                tx1.Text = cell.ToString();
                ICell cell11 = titlerow.GetCell(i);
                lab1.Text = cell11.ToString();
            }

            double total = 0;
            for (int i = 1; i < 10; i++)
            {
                tx1 = (TextBox)this.Controls.Find("textBox" + i.ToString(), true)[0];
                total = total + double.Parse(tx1.Text);

            }

            label11.Text = "总金额:" + total.ToString();

 

 

        }
    }
}