需求：选择一个Excel文件，然后对该Excel文件进行处理，再导出一个处理后的Excel文件。

效果图

声明：我对winform开发不熟，但是我看到许多开发人员做东西只管交差，从不考虑用户体验，也不考虑容错处理，我就在想，难道就不能做得专业一点吗？当你用别人做的东西，满口吐槽的时候有没有想过别人用你做的东西的时候，会不会一样的狂喷呢？

这里对Excel的操作使用了NPOI.dll组件，可自行去网上现在或者使用NuGet下载。

界面皮肤

IrisSkin4.dll包括(73皮肤+vs2012兼容) 绿色版下载地址：http://pan.baidu.com/s/1eQ1sAUA

这里使用到了IrisSkin4.dll皮肤控件

使用方法：

1、添加IrisSkin4.dll引用

1、工具箱，添加此程序集

2、复制皮肤文件

设置皮肤文件的属性

3、代码调用

        public frmMain()

        {

            InitializeComponent();

            //加载皮肤

            skinEngine1.SkinFile ="Skins/Warm.ssk";

            skinEngine1.Active = true;

            skinEngine1.SkinDialogs = false;
            //如果要让某个控件不使用皮肤，则设置此属性，这样，就可以单独为此控件设置属性了，否则为此控件设置的属性将会被皮肤属性覆盖

            lblShow.Tag = skinEngine1.DisableTag;

            lblMsg.Tag = skinEngine1.DisableTag;

            this.lblShow.ForeColor = Color.Red;

            this.lblMsg.ForeColor = Color.Green;

        }

关于excel的操作，这里还是使用NPOI.dll，可以自己从网上下载，也可以直接从vs的NuGet中下载。

需要注意的是，对于一些比较耗时的界面操作，建议使用一个进度条，然后以异步调用的形式进行操作。异步调用可以开启一个线程，如果在线程调用的代码中需要修改窗体控件，也就是要修改主线程的内容，可以使用如下代码：

Invoke(new MethodInvoker(delegate { progressBar.Maximum = sheet.LastRowNum; }));}

代码很简单，这里我不做过多的说明，详情请参见代码。

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 = ;

            var sheetCount = workbook.NumberOfSheets;

            int successCounts = ;

            for (int sheetIndex = ; sheetIndex < sheetCount; sheetIndex++)

            {

                var sheetIndexShow = sheetIndex + ;

                var sheet = workbook.GetSheetAt(sheetIndex);

                //progressBar.Maximum = sheet.LastRowNum;

                if (sheetIndex == )

                {

                    Invoke(new MethodInvoker(delegate { progressBar.Maximum = sheet.LastRowNum; }));

                }

                IsCalculating = true;

                for (int i = ; i <= sheet.LastRowNum; i++)

                {

                    var row = sheet.GetRow(i);

                    var weight = row.GetCell() == null ? 0D : row.GetCell().NumericCellValue; //重量

                    var targetProvince = row.GetCell() == null ? "" : row.GetCell().StringCellValue.ToString().Trim(); //省份

                    if (targetProvince.IsNullOrEmpty())

                    {

                        Invoke(new MethodInvoker(delegate { lblShow.Text += String.Format("{0}  邮件号: {1} 缺少目标地省份!\r\n", lackProvinceCount.ToString(""), row.GetCell().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());

                        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());

                        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() > )

                    {

                        foreach (var v in result)

                        {

                            if (v["CalStyle"].ToString() == "Y")

                            {

                                yWeighFee = v["SalesPrice"].ToString();

                            }

                            else if (v["CalStyle"].ToString() == "P")

                            {

                                pWeighFee = result[]["SalesPrice"].ToString();

                            }

                        }

                    }

                    else

                    {

                        setCellStyle(workbook, row.GetCell());

                        continue;

                    }

                    successCounts++;

                    // 执行运费计算 " EXEC p_CalculatePriceByCH CountryID, Weight, ChannelID, CalFlag ";

                    var sExecProc = String.Format(" EXEC p_CalculatePriceByCH {0}, {1}, {2}, {3} ", countryID, weight, channelID, );

                    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();

                    cellpWeighFee.SetCellType(CellType.Numeric);

                    cellpWeighFee.SetCellValue(pWeighFee.ToDouble());

                    //续重费用 12

                    var cellyWeighFee = row.GetCell();

                    cellyWeighFee.SetCellType(CellType.Numeric);

                    cellyWeighFee.SetCellValue(yWeighFee.ToDouble());

                    //OVS运费 13

                    var cellShipFee = row.GetCell();

                    cellShipFee.SetCellType(CellType.Numeric);

                    cellShipFee.SetCellValue(shipFeeRs.ToDouble());

                    //操作费 14

                    var cellOrderFee = row.GetCell();

                    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();

                }

                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;

            }

        }

    }

}

App.config

<?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>

最新文章

  1. springboot中swaggerUI的使用
  2. Java 判断文件夹、文件是否存在、否则创建文件夹
  3. 《理解 ES6》阅读整理：函数（Functions）（七）Block-Level Functions
  4. Winform如何实现ComboBox模糊查询
  5. 【linux】ubuntu stmp服务器配置
  6. PLSQL在64位系统连接不上32位的服务器
  7. C# 遍历DLL导出函数
  8. C语言程序设计现代方法_基本类型(第七章)
  9. 精益求精， ePub 电子书制作手记
  10. asp下实现多条件模糊查询SQL语句
  11. C的xml编程文章链接
  12. python服务器环境搭建（2）——安装相关软件
  13. Day15 Javascipt内容补充
  14. TreeView 节点拖拽
  15. python asyncio
  16. Boot-col-sm布局
  17. python多版本控制
  18. CentOS 6.4 命令行 安装 VMware Tools
  19. windows运行打开服务命令
  20. 前后台交互（打开前端页面，不传递任何数据，发送ajax请求）

热门文章

  1. 更新日志 - BugHD 新增邮件告警功能
  2. ValidationSummary控件不弹出错误提示框
  3. Html标签之frameset&amp;图片切换
  4. 【原创】探索Newlife X组件利器之：XCoder点滴[附下载]
  5. 总结整理 -- ruby系列
  6. Hadoop阅读笔记（六）——洞悉Hadoop序列化机制Writable
  7. sublime 插件总结
  8. KafkaConfig介绍
  9. [New Portal]Windows Azure Virtual Machine (16) 使用Azure PowerShell创建Azure Virtual Machine
  10. JS魔法堂：IMG元素加载行为详解