wordpress调用网站副标题,淄博网赢网站建设,物流怎么弄网站,一个完整的网站怎么做需求#xff1a;选择一个Excel文件#xff0c;然后对该Excel文件进行处理#xff0c;再导出一个处理后的Excel文件。 效果图 声明#xff1a;我对winform开发不熟#xff0c;但是我看到许多开发人员做东西只管交差#xff0c;从不考虑用户体验#xff0c;也不考虑容错处… 需求选择一个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/9public 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 nameworkbook/param/// param namecell/paramprivate 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));//首重费用 11var cellpWeighFee row.GetCell(11);cellpWeighFee.SetCellType(CellType.Numeric);cellpWeighFee.SetCellValue(pWeighFee.ToDouble());//续重费用 12var cellyWeighFee row.GetCell(12);cellyWeighFee.SetCellType(CellType.Numeric);cellyWeighFee.SetCellValue(yWeighFee.ToDouble());//OVS运费 13var cellShipFee row.GetCell(13);cellShipFee.SetCellType(CellType.Numeric);cellShipFee.SetCellValue(shipFeeRs.ToDouble());//操作费 14var 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.Textex.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;}}}
} View Code App.config ?xml version1.0?
configurationappSettingsadd keyconnectionString valueData Source192.xx.2.xxx;Initial Catalogxxx;Persist Security InfoTrue;User IDxx;Passwordxxxx/add keyEnbleLog valuefalse/!--是否开启日志true,false--add keyLogUrl valueD://add keyLogName value计费日志.txt/add keythemeName valueWarm.ssk/!--结算方式A一--!--渠道--add keyproviceNameA value上海,江苏,浙江,北京,安徽/!--这些省走经济快递渠道--add keychannelAE valueGZExpress_E/ !--经济快递渠道代码--add keychannelAS valueGZExpress_S/ !--标准快递渠道代码--!--结算方式B 帝途一--add keyproviceNameB value北京,上海,江苏,浙江,天津/!--这些省走经济快递渠道--add keychannelBE valueGZExpress_E1/!--经济快递渠道代码--add keychannelBS valueGZExpress_S1/!--标准快递渠道代码--/appSettingsstartupsupportedRuntime versionv4.0 sku.NETFramework,Versionv4.5//startupruntimeassemblyBinding xmlnsurn:schemas-microsoft-com:asm.v1dependentAssemblyassemblyIdentity nameNPOI publicKeyToken0df73ec7942b34e1 cultureneutral/bindingRedirect oldVersion0.0.0.0-2.1.3.1 newVersion2.1.3.1//dependentAssemblydependentAssemblyassemblyIdentity nameNPOI.OOXML publicKeyToken0df73ec7942b34e1 cultureneutral/bindingRedirect oldVersion0.0.0.0-2.1.3.1 newVersion2.1.3.1//dependentAssembly/assemblyBinding/runtime
/configuration View Code 转载于:https://www.cnblogs.com/jiekzou/p/4425642.html
