需求：选择一个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>