C# Excel处理工具

using Dapper;using NExtensions;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using System;using System.ComponentModel;using System.Configuration;using System.Data.SqlClient;using System.Diagnostics;using System.IO;using System.Linq;using System.Threading;using System.Windows.Forms;using System.Drawing;using System.Collections.Generic;using NPOI.HSSF.Util;namespace uuch.CustomsCheckPrint{    //modify by:Zouqj  2015/4/9    public partial class frmMain : Form    {        public frmMain()        {            InitializeComponent();            //加载皮肤                          skinEngine1.SkinFile ="Skins/"+ConfigurationManager.AppSettings["themeName"];            skinEngine1.Active = true;            skinEngine1.SkinDialogs = false;            lblShow.Tag = skinEngine1.DisableTag;            lblMsg.Tag = skinEngine1.DisableTag;            this.lblShow.ForeColor = Color.Red;            this.lblMsg.ForeColor = Color.Green;        }        public bool IsCalculating { get; set; }        /// <summary>        /// 设置单元格样式        /// </summary>        /// <param name="workbook"></param>        /// <param name="cell"></param>        private void setCellStyle(IWorkbook workbook, ICell cell)        {            HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();            HSSFFont ffont = (HSSFFont)workbook.CreateFont();            //ffont.FontHeight = 20 * 20;            //ffont.FontName = "宋体";            ffont.Color = HSSFColor.Red.Index;            fCellStyle.SetFont(ffont);            //fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐            //fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐            cell.CellStyle = fCellStyle;        }        public void EditAndSave(string filePath, ProgressBar progressBar, Label label)        {            // 0       ,    1 ,     2 , 3,    4,    5,   6            // 收寄日期, 邮件号, 寄达地, 类, 重量, 邮费, 省份            //序号    提单号    快件单号    发件人    收件人    收件人地址    内件名称    数量    价值(USD)    重量(KG)    省份    首重费用    续重费用    OVS运费    操作费    OVS税费            var sc = new SqlConnection(ConfigurationManager.AppSettings["connectionString"]);            sc.Open();            IWorkbook workbook = null;            var fileExten = Path.GetExtension(filePath);            var fsRead = File.OpenRead(filePath);            if (fileExten == ".xls"||fileExten == ".xlsx")            {                workbook = new HSSFWorkbook(fsRead);            }            else            {                MessageBox.Show("文件不是有效的Excel文件!");                return;            }            string proviceNameA = ConfigurationManager.AppSettings["proviceNameA"];            string proviceNameB = ConfigurationManager.AppSettings["proviceNameB"];            string[] economicProvince =null;            string channelE = string.Empty; //经济渠道            string channelS = string.Empty; //标准渠道            if (rbtnA.Checked)            {                channelE = ConfigurationManager.AppSettings["channelAE"];                channelS = ConfigurationManager.AppSettings["channelAS"];                economicProvince = string.IsNullOrEmpty(proviceNameA) ? null : proviceNameA.Split(',');            }            else if (rbtnB.Checked)            {                channelE = ConfigurationManager.AppSettings["channelBE"];                channelS = ConfigurationManager.AppSettings["channelBS"];                economicProvince = string.IsNullOrEmpty(proviceNameB) ? null : proviceNameB.Split(',');            }            string pWeighFee = string.Empty; //首重费            string yWeighFee = string.Empty; //续重费用            string orderFee = string.Empty; //订单费 操作费            fsRead.Close();            //ISheet sheet = workbook.GetSheetAt(0);            var lackProvinceCount = 1;            var sheetCount = workbook.NumberOfSheets;            int successCounts = 0;            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)            {                var sheetIndexShow = sheetIndex + 1;                var sheet = workbook.GetSheetAt(sheetIndex);                //progressBar.Maximum = sheet.LastRowNum;                if (sheetIndex == 0)                {                    Invoke(new MethodInvoker(delegate { progressBar.Maximum = sheet.LastRowNum; }));                }                IsCalculating = true;                for (int i = 1; i <= sheet.LastRowNum; i++)                {                    var row = sheet.GetRow(i);                    var weight = row.GetCell(9) == null ? 0D : row.GetCell(9).NumericCellValue; //重量                    var targetProvince = row.GetCell(10) == null ? "" : row.GetCell(10).StringCellValue.ToString().Trim(); //省份                    if (targetProvince.IsNullOrEmpty())                    {                        Invoke(new MethodInvoker(delegate { lblShow.Text += String.Format("{0}  邮件号: {1} 缺少目标地省份!\r\n", lackProvinceCount.ToString("00000"), row.GetCell(1).StringCellValue); }));                                             lackProvinceCount++;                        continue;                    }                    if (targetProvince.Contains("省"))                    {                        targetProvince = targetProvince.Replace("省", "");                    }                    var channelCode = economicProvince.Contains(targetProvince) == true ? channelE : channelS; //根据省份获取渠道代码                             var sSelectProvince = String.Format("SELECT Base_PlaceID FROM Base_Place WHERE CnName LIKE '%{0}%' ", targetProvince);                    var resultContry = sc.Query(sSelectProvince, null).FirstOrDefault();                    if (resultContry == null)                    {                        setCellStyle(workbook, row.GetCell(10));                        continue;                    }                    var countryID = resultContry["Base_PlaceID"].ToString();                    var sSelectChannel = String.Format("SELECT Base_ChannelInfoID FROM Base_ChannelInfo WHERE ChannelCode = '{0}' ", channelCode);                    var resultChannel= sc.Query(sSelectChannel, null).FirstOrDefault();                    if (resultChannel == null)                    {                        setCellStyle(workbook, row.GetCell(10));                        continue;                    }                    var channelID = resultChannel["Base_ChannelInfoID"].ToString();                    string sql = string.Format(@"select SalesPrice, CalStyle  from v_Price_PriceInfo where SubChannelCode='{0}' and charindex ('{1}',AreaCountry)>0", channelCode, countryID);                    // 销售价 计算方式 P：首重 Y：续重                    var result = sc.Query(sql, null).ToList();                    if (result != null && result.Count() > 0)                    {                        foreach (var v in result)                        {                            if (v["CalStyle"].ToString() == "Y")                            {                                yWeighFee = v["SalesPrice"].ToString();                            }                            else if (v["CalStyle"].ToString() == "P")                            {                                pWeighFee = result[0]["SalesPrice"].ToString();                            }                        }                    }                    else                    {                        setCellStyle(workbook, row.GetCell(10));                        continue;                    }                    successCounts++;                    // 执行运费计算 " EXEC p_CalculatePriceByCH CountryID, Weight, ChannelID, CalFlag ";                    var sExecProc = String.Format(" EXEC p_CalculatePriceByCH {0}, {1}, {2}, {3} ", countryID, weight, channelID, 1);                    var query = sc.Query(sExecProc, null).FirstOrDefault();                    var shipFeeRs = query["BaseFee"].ToString();                    orderFee = query["OrderFee"].ToString();                    //Trace.WriteLine(String.Format("{0} - {1}, {2} - {3}, {4} ", targetProvince, countryID, channelCode, channelID, sExecProc));                    //首重费用 11                    var cellpWeighFee = row.GetCell(11);                    cellpWeighFee.SetCellType(CellType.Numeric);                    cellpWeighFee.SetCellValue(pWeighFee.ToDouble());                    //续重费用 12                    var cellyWeighFee = row.GetCell(12);                    cellyWeighFee.SetCellType(CellType.Numeric);                    cellyWeighFee.SetCellValue(yWeighFee.ToDouble());                    //OVS运费 13                    var cellShipFee = row.GetCell(13);                    cellShipFee.SetCellType(CellType.Numeric);                    cellShipFee.SetCellValue(shipFeeRs.ToDouble());                    //操作费 14                    var cellOrderFee = row.GetCell(14);                    cellOrderFee.SetCellType(CellType.Numeric);                    cellOrderFee.SetCellValue(orderFee.ToDouble());                    //progressBar.Value = i;                    Invoke(new MethodInvoker(delegate { lblMsg.Text = String.Format("工作表: {0}/{1} | 行: {2}/{3}", sheetIndexShow, sheetCount, i, sheet.LastRowNum); progressBar1.Value = i; }));  //异步显示进度条                    System.Windows.Forms.Application.DoEvents();                }                Invoke(new MethodInvoker(delegate { lblShow.Text = string.Format("计算成功！成功数：{0}", successCounts); }));                if (sheet.LastRowNum != successCounts)                {                    Invoke(new MethodInvoker(delegate { lblShow.Text += string.Format(" 有计算不出的数据{0}条，请核对数据或格式是否有误！", sheet.LastRowNum - successCounts); }));                }            }            var fsSave = File.Create(textBoxOutputPath.Text);            workbook.Write(fsSave);            fsSave.Close();            sc.Close();            IsCalculating = false;        }        protected override void OnClosing(CancelEventArgs e)        {            if (IsCalculating)            {                var rs = MessageBox.Show("计算还没结束, 确定退出?", "确定退出?", MessageBoxButtons.YesNoCancel);                if (rs == DialogResult.Yes)                {                    this.Dispose();                    this.Close();                    Environment.Exit(0);                }                else                {                    e.Cancel = true;                }            }        }        private void btnSave_Click(object sender, EventArgs e)        {            if (string.IsNullOrEmpty(textBoxInputFilePath.Text))            {                lblShow.Text = "请先选择要计算的文件！\r\n";                return;            }            if (string.IsNullOrEmpty(textBoxOutputPath.Text))            {                lblShow.Text = "必须制定输出文件路径和名称！\r\n";                return;            }            lblShow.Text = "";            //两种结算方式            try            {                Thread t = new Thread(new ThreadStart(Single));                t.IsBackground = true;                t.Start();              }            catch (Exception ex)            {                LogAPI.WriteLog(ex.Message);                lblShow.Text=ex.Message;//"计算错误，详情请查看日志！"                return;            }            Trace.WriteLine("OK");        }        //适配器        void Single()        {            EditAndSave(textBoxInputFilePath.Text, progressBar1, lblMsg);        }        private void buttonChooseInputFile_Click(object sender, EventArgs e)        {            var fileDialog = new OpenFileDialog();            fileDialog.Multiselect = true;            fileDialog.Title = "选择文件";            fileDialog.Filter = "Excel files|*.xls; *.xlsx";            if (fileDialog.ShowDialog() == DialogResult.OK)            {                textBoxInputFilePath.Text = fileDialog.FileName;            }        }        private void buttonChooseOutputFolder_Click(object sender, EventArgs e)        {            var saveFileDialog = new SaveFileDialog();            saveFileDialog.Title = "保存文件";            saveFileDialog.Filter = "Excel files|*.xls; *.xlsx";            if (saveFileDialog.ShowDialog() == DialogResult.OK)            {                textBoxOutputPath.Text = saveFileDialog.FileName;            }        }    }}

<?xml version="1.0"?><configuration>    <appSettings>      <add key="connectionString" value="Data Source=192.xx.2.xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xx;Password=xxxx"/>      <add key="EnbleLog" value="false"/><!--是否开启日志：true,false-->      <add key="LogUrl" value="D:/"/>      <add key="LogName" value="计费日志.txt"/>      <add key="themeName" value="Warm.ssk"/>      <!--结算方式A一-->      <!--渠道-->      <add key="proviceNameA" value="上海,江苏,浙江,北京,安徽"/><!--这些省走经济快递渠道-->      <add key="channelAE" value="GZExpress_E"/> <!--经济快递渠道代码-->      <add key="channelAS" value="GZExpress_S"/> <!--标准快递渠道代码-->      <!--结算方式B 帝途一-->      <add key="proviceNameB" value="北京,上海,江苏,浙江,天津"/><!--这些省走经济快递渠道-->      <add key="channelBE" value="GZExpress_E1"/>      <!--经济快递渠道代码-->      <add key="channelBS" value="GZExpress_S1"/>      <!--标准快递渠道代码-->    </appSettings>    <startup>        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>    </startup>    <runtime>        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">            <dependentAssembly>                <assemblyIdentity name="NPOI" publicKeyToken="0df73ec7942b34e1" culture="neutral"/>                <bindingRedirect oldVersion="0.0.0.0-2.1.3.1" newVersion="2.1.3.1"/>            </dependentAssembly>            <dependentAssembly>                <assemblyIdentity name="NPOI.OOXML" publicKeyToken="0df73ec7942b34e1" culture="neutral"/>                <bindingRedirect oldVersion="0.0.0.0-2.1.3.1" newVersion="2.1.3.1"/>            </dependentAssembly>        </assemblyBinding>    </runtime></configuration>