/// <summary>
/// 导出txt表格说明
/// </summary>
/// <param name="types"></param>
public static void GenerateTableDes(params Type[] types)
{
var strDesc = XlsxHelper.GetDecsipt(types);
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "记事本|*.txt"; //设置文件类型
saveFile.Title = "导出说明文件"; //设置标题
saveFile.AddExtension = true; //是否自动增加所辍名
saveFile.AutoUpgradeEnabled = true; //是否随系统升级而升级外观
if (saveFile.ShowDialog() == DialogResult.OK) //如果点的是确定就得到文件路径
{
File.WriteAllText(saveFile.FileName, strDesc);
}
}
private void lkDecs_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
var strDesc = XlsxHelper.GetDecsipt(typeof(_Form5延时报表), typeof(_Form5缺货延时报表判断));
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "记事本|*.txt"; //设置文件类型
saveFile.Title = "导出说明文件"; //设置标题
saveFile.AddExtension = true; //是否自动增加所辍名
saveFile.AutoUpgradeEnabled = true; //是否随系统升级而升级外观
if (saveFile.ShowDialog() == DialogResult.OK) //如果点的是确定就得到文件路径
{
File.WriteAllText(saveFile.FileName, strDesc);
}
}
public Cell GetCell(XlsxCell xlsxCell)
{
Cell cell = new Cell();
object value = xlsxCell.Value;
string valueType = value.GetType().Name;
CellValue cellValue = new CellValue();
cell.StyleIndex = StylesheetManager.GetStyleIndex(xlsxCell.IsDateWithoutTime ? "Date" : valueType, xlsxCell);
switch (valueType)
{
case "Int32":
cellValue.Text = xlsxCell.Value.ToString();
break;
case "Int64":
case "Single":
case "Double":
XlsxHelper.SetContextEnglish();
cellValue.Text = xlsxCell.Value.ToString();
XlsxHelper.ResetContext();
break;
case "String":
cellValue.Text = SharedStringManager.GetIdByValue(value.ToString());
cell.DataType = CellValues.SharedString;
break;
case "DateTime":
double date = ((DateTime)value).ToOADate();
XlsxHelper.SetContextEnglish();
cellValue.Text = date.ToString();
XlsxHelper.ResetContext();
break;
case "Boolean":
//todo handle boolean
break;
default:
break;
}
cell.CellValue = cellValue;
return(cell);
}
private void ExportExcel(List <EX订单分配> buyerDataList, List <EX订单分配> devDataList, List <EX工作量> workLoadList)
{
var strOpMsg = string.Empty;
var buyerBuffer = XlsxHelper.SimpleWrite(buyerDataList.Select(x => x.ToDictionary()).ToList(), out strOpMsg);
var devBuffer = XlsxHelper.SimpleWrite(devDataList.Select(x => x.ToDictionary()).ToList(), out strOpMsg);
var workloadBuffer = XlsxHelper.SimpleWrite(workLoadList.Select(x => x.ToDictionary()).ToList(), out strOpMsg);
InvokeMainForm((obj) =>
{
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "Excel 工作簿|*.xlsx"; //设置文件类型
saveFile.Title = "导出数据"; //设置标题
saveFile.AddExtension = true; //是否自动增加所辍名
saveFile.AutoUpgradeEnabled = true; //是否随系统升级而升级外观
if (saveFile.ShowDialog() == DialogResult.OK) //如果点的是确定就得到文件路径
{
var FileName = saveFile.FileName; //得到文件路径
var saveFilName = Path.GetFileNameWithoutExtension(FileName);
var savePath = Path.GetDirectoryName(FileName);
var FileName2 = Path.Combine(savePath, saveFilName + "工作量.xlsx");
var FileName3 = Path.Combine(savePath, saveFilName + "(开发订单).xlsx");
txtExport.Text = FileName;
try
{
XlsxHelper.SaveWorkBook(buyerBuffer, FileName, out strOpMsg);
XlsxHelper.SaveWorkBook(workloadBuffer, FileName2, out strOpMsg);
XlsxHelper.SaveWorkBook(devBuffer, FileName3, out strOpMsg);
}
catch (Exception ex)
{
ShowMsg(ex.Message);
}
ShowMsg("表格生成完毕");
btnAnalyze.Enabled = false;
}
}, null);
}
protected XlWriterBase(string filePath, Application oxlApp)
{
XlHelper = new XlsxHelper(oxlApp, filePath);
}
private void btnAnalyze_Click(object sender, EventArgs e)
{
try
{
var strOpMsg = string.Empty;
var dDiviAmount = Convert.ToDouble(NtxtAmount.Value);
var strImExcelPath = txtUpload.Text;
var imDataList = new List <IM订单分配>();
var exDataList_采购 = new List <EX订单分配>();
var exDataList_开发 = new List <EX订单分配>();
var exWorkLoad = new List <EX工作量>();
var providerNames = new List <string>();//供应商唯一队列
var buyerNames = new List <string>();
#region 解析表格数据
var actRead = new Action(() =>
{
ShowMsg("开始读取表格数据");
using (var fs = new FileStream(strImExcelPath, FileMode.Open))
{
imDataList = XlsxHelper.Read <IM订单分配>(fs, IM订单分配.GetMapping(), out strOpMsg);
}
});
#endregion
#region 数据处理
actRead.BeginInvoke((obj) =>
{
ShowMsg("开始计算表格数据");
//供应商唯一取值
providerNames = imDataList.Select(p => p._供应商).Where(p => !string.IsNullOrEmpty(p)).Distinct().OrderBy(p => p).ToList();
//计算供应商采购金额
providerNames.ForEach(pd =>
{
var curProviderSku = imDataList.Where(w => w._供应商 == pd).ToList();
var thisProviderAmount = curProviderSku.Select(c => c._采购金额).Sum();
//小于分界,分给合肥
if (thisProviderAmount <= dDiviAmount)
{
curProviderSku.ForEach(sk =>
{
var curOrder = new EX订单分配();
curOrder._供应商 = pd;
curOrder._SKU = sk._SKU;
curOrder._Qty = sk._建议采购数量;
curOrder._采购员 = Helper.ChangeLowerBuyer(sk._采购员);
curOrder._含税单价 = sk._商品成本单价;
curOrder._制单人 = sk._采购员;
curOrder._对应供应商采购金额 = thisProviderAmount;
if (Helper.IsBuyer(sk._采购员))
{
exDataList_采购.Add(curOrder);
}
else
{
exDataList_开发.Add(curOrder);
}
});
}
else
{
//大于分界,保存不变
curProviderSku.ForEach(sk =>
{
var curOrder = new EX订单分配();
curOrder._供应商 = pd;
curOrder._SKU = sk._SKU;
curOrder._Qty = sk._建议采购数量;
curOrder._采购员 = sk._采购员;
curOrder._含税单价 = sk._商品成本单价;
curOrder._制单人 = curOrder._采购员;
curOrder._对应供应商采购金额 = thisProviderAmount;
if (Helper.IsBuyer(sk._采购员))
{
exDataList_采购.Add(curOrder);
}
else
{
exDataList_开发.Add(curOrder);
}
});
}
});
//计算工作量
buyerNames.AddRange(exDataList_采购.Where(x => !string.IsNullOrEmpty(x._采购员)).Select(x => x._采购员).Distinct().ToList());
buyerNames.ForEach(b =>
{
var refOrders = exDataList_采购.Where(m => m._采购员 == b).ToList();
var amount = refOrders.Select(m => m._供应商).Distinct().Count();
exWorkLoad.Add(new EX工作量()
{
_采购员 = b, _订单量 = amount
});
});
#region 导出数据
ExportExcel(exDataList_采购, exDataList_开发, exWorkLoad);
#endregion
}, null);
#endregion
}
catch (Exception ex)
{
ShowMsg(ex.Message);
}
}
