需求：选择一个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 = 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} 缺少目标地省份! ", 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 = "请先选择要计算的文件！ "; return; } if (string.IsNullOrEmpty(textBoxOutputPath.Text)) { lblShow.Text = "必须制定输出文件路径和名称！ "; 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>