索益Mike的excel商品操作

索益Mike的excel商品操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using NPOI;
using NPOI.SS.UserModel;

namespace Web.Excel
{
    public partial class t1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string importExcelPath = "E:\\1.xlsx";
            string exportExcelPath = "E:\\export.xlsx";
            IWorkbook workbook = WorkbookFactory.Create(importExcelPath);
            ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作薄

            //数据全部加载到ds中，开始分析。
            //找出列数Name	Subtotal	Shipping	Total	Shipping Method	Lineitem quantity	Lineitem name
            int Name, Subtotal, Shipping, Total, ShippingMethod, Lineitemquantity, Lineitemname, count = 0;
            Name = Subtotal = Shipping = Total = ShippingMethod = Lineitemquantity = Lineitemname = -1;

            IRow row = (IRow)sheet.GetRow(0);//获取第一行
            //ICell cell = (ICell)row.GetCell(0);
            //string s = cell.ToString();
            // Response.Write("<script>alert('表格内没有数据"+ s + "');</script>");
            //设置第一行第一列值,更多方法请参考源官方Demo
            //row.CreateCell(0).SetCellValue("test1");//设置第一行第一列值

            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = (ICell)row.GetCell(i);
                string s = cell.ToString();
                //Response.Write("<script>alert('" + s + "');</script>");
                if (s == "Name")
                {
                    Name = i; count++;
                }
                if (s == "Subtotal")
                {
                    Subtotal = i; count++;
                }
                if (s == "Shipping")
                {
                    Shipping = i; count++;
                }
                if (s == "Total")
                {
                    Total = i; count++;
                }
                if (s == "Shipping Method")
                {
                    ShippingMethod = i; count++;
                }
                if (s == "Lineitem quantity")
                {
                    Lineitemquantity = i; count++;
                }
                if (s == "Lineitem name")
                {
                    Lineitemname = i; count++;
                }
            }
            if (count != 7)
            {
                Response.Write("<script>alert('7个没有都找到');</script>");
            }
            else
            {
                //Response.Write("<script>alert('" + Lineitemquantity + Name + Lineitemname + "');</script>");
            }
            count = 0;

            //解决tip问题
            for(int i=1;i<= sheet.LastRowNum; i++)
            {
                row = (IRow)sheet.GetRow(i);//获取行
                ICell cell = (ICell)row.GetCell(Lineitemname);
                string s = cell.ToString();
                if (s== "Tip")
                {
                    //获取下一行的Lineitem name和Lineitem quantity填充本行
                    IRow rowNext = (IRow)sheet.GetRow(i+1);//获取行

                    ICell cellNext = (ICell)rowNext.GetCell(Lineitemname);
                    string sNext = cellNext.ToString();
                    row.CreateCell(Lineitemname).SetCellValue(sNext);//用下一行名称填充本行

                    cellNext = (ICell)rowNext.GetCell(Lineitemquantity);
                    sNext =cellNext.ToString();
                    row.CreateCell(Lineitemquantity).SetCellValue(Convert.ToInt32(sNext));//用下一行数量填充本行

                    sheet.ShiftRows(i+2, sheet.LastRowNum, -1);
                    //sheet.shiftRows(startRow, endRow, shiftCount)参数说明:
                    //其含义是指从startRow EndRow往上移动或者是往下移动，当shiftcount为 - 1时表示的是上移，当为正时表示的是下移，通过移动来彻底删除。

                }
                count++;
            }
            Response.Write("<script>alert('"+ count + "');</script>");
            for (int i = 1; i < sheet.LastRowNum; i++)
            {
                row = (IRow)sheet.GetRow(i);//获取本行
                ICell cell = (ICell)row.GetCell(Name);
                string name = cell.ToString();
                cell = (ICell)row.GetCell(Lineitemquantity);
                int quantity =Convert.ToInt32(cell.ToString());

                IRow rowNext = (IRow)sheet.GetRow(i+1);//获取下一行
                ICell cellNext = (ICell)rowNext.GetCell(Name);
                string nameNext = cellNext.ToString();
                cellNext = (ICell)rowNext.GetCell(Lineitemquantity);
                int quantityNext = Convert.ToInt32(cellNext.ToString());

                if(name == nameNext)
                {
                   // Response.Write("<script>alert('1');</script>");
                    row.CreateCell(Lineitemquantity).SetCellValue(quantity+ quantityNext);
                    if (sheet.LastRowNum < i + 2)//处理最后一单订单多行问题
                    {
                        sheet.ShiftRows(0, sheet.LastRowNum-1, 1);
                        sheet.ShiftRows(1, sheet.LastRowNum, -1);
                        break;
                    } 
                    sheet.ShiftRows(i + 2, sheet.LastRowNum, -1);
                    i--;
                }
            }
                //导出excel
            FileStream fs = new FileStream(exportExcelPath, FileMode.Create, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Close();
        }
    }
}