private void LoadButton_Click(object sender, RoutedEventArgs e)
{
SetProgressBar(0);
try
{
OpenFileDialog dlg = new OpenFileDialog();
var result = dlg.ShowDialog(); // Show the dialog.
if (result == true)
{
string file = dlg.FileName;
SourceFileTextBox.Text = file;
try
{
ShowStatusText(Wording.LoadingFile);
ThreadPool.QueueUserWorkItem(o =>
{
var loadFile = ExcelTool.GetExcelFile(file);
if (loadFile.Success)
{
Dt = loadFile.Success ? (DataTable)loadFile.Data : null;
//Set progress bar if have data
if (Dt != null && Dt.Rows.Count > 0)
{
SetupProgressBar(Dt.Rows.Count * 2 + 2);
SetSaveEnable(true);
SetSaveLocation(file.Substring(0, file.LastIndexOf(".", StringComparison.Ordinal)) + "_" + DateTime.Now.ToFileTime() + ".xlsx");
ShowStatusText(string.Format(Wording.LoadSuccessGotrows, Dt.Rows.Count));
}
else
{
ShowStatusText(Wording.LoadFail);
}
}
else
{
ShowStatusText(loadFile.Message);
}
});
}
catch (Exception ex)
{
var msg = string.Format(Wording.OpenFileError, ex.Message);
ShowStatusText(msg);
Debug.WriteLine(msg);
MessageBox.Show(msg);
}
}
}
catch (Exception exception)
{
ShowStatusText(exception.Message);
Console.WriteLine(exception);
}
}
/// <summary>
/// doing save work
/// </summary>
/// <param name="location"></param>
/// <param name="sales"></param>
/// <param name="salesAssustant"></param>
/// <param name="shipping"></param>
/// <param name="priority"></param>
/// <param name="remark"></param>
/// <param name="manufacturer"></param>
/// <param name="material"></param>
/// <param name="issueDate"></param>
/// <param name="ad">additional option 1~5</param>
/// <param name="lineHeight"></param>
private void SaveExcel(string location, string sales, string salesAssustant, bool shipping, bool priority, bool remark, bool manufacturer, bool material, bool issueDate, bool[] ad, int lineHeight)
{
var excelApp = new Excel.Application();//{Visible = true};
var workbooks = excelApp.Workbooks;
try
{
workbooks.Add();
}
catch (Exception e)
{
MessageBox.Show("Exception Message: " + e.Message);
if (e.InnerException != null)
{
MessageBox.Show("InnerException Message: " + e.InnerException.Message);
}
MessageBox.Show("Exception Trace : " + e.StackTrace);
excelApp.Quit();
return;
}
#region first sheet
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
workSheet.Name = "ForKeyIn";
//add head
for (int i = 0; i < Models.FuhsunHeaders.Length; i++)
{
workSheet.Cells[1, 1 + i] = Models.FuhsunHeaders[i];
}
#region find adidas data put into cell
for (var i = 0; i < Dt.Rows.Count; i++)
{
//check empty row
if (string.IsNullOrEmpty(Dt.Rows[i].Field <string>(1)) || string.IsNullOrEmpty(Dt.Rows[i].Field <string>(2)))
{
continue;
}
foreach (var m in Models.AdidasToFuhsunMapping)
{
var value = Dt.Rows[i].Field <string>(ExcelTool.LettersToInt(m.Value) - 1);
//unit formatting
if (m.Value == "BP" && value == "YARD")
{
value = "YRD";
}
//contact person
if (m.Value == "O")
{
var r = Regex.Match(value, @"[a-zA-Z0-9.]*");
if (r.Length > 0)
{
value = r.Value;
}
}
//Material color to color code/desc
if (m.Value == "BM")
{
var ar = value.Split(new[] { ';', '/' }, StringSplitOptions.RemoveEmptyEntries);
var colorCode = new List <string>();
var colorDesc = new List <string>();
foreach (var t in ar)
{
var s = t.Trim();
if (s.Length > 5)
{
colorCode.Add(s.Substring(s.Length - 4, 4));
colorDesc.Add(s.Substring(0, s.Length - 4).Trim());
}
//only multi colorCode
else
{
colorCode.Add(t);
}
}
//try make sure the format is right
if (colorCode.Count > 0 && colorCode.Count == colorDesc.Count)
{
value = string.Join("/", colorCode);
workSheet.Cells[2 + i, "N"] = string.Join("/", colorDesc);
}
//only code
else
{
value = string.Join("/", colorCode);
}
}
workSheet.Cells[2 + i, m.Key] = value;
}
#region season
var seasonColume = Dt.Rows[i].Field <string>(ExcelTool.LettersToInt("CE") - 1);
if (!string.IsNullOrEmpty(seasonColume))
{
var season = seasonColume.Split('-');
if (season.Length > 1)
{
workSheet.Cells[2 + i, "V"] = season[1];
workSheet.Cells[2 + i, "W"] = season[0];
}
else
{
workSheet.Cells[2 + i, "V"] = seasonColume;
workSheet.Cells[2 + i, "W"] = seasonColume;
}
}
#endregion
#region contact person
#endregion
//TODO add custom input info
//Sales
workSheet.Cells[2 + i, "AU"] = sales;
workSheet.Cells[2 + i, "AV"] = salesAssustant;
StepProgressBar();
}
#endregion
for (var j = 1; j <= Models.AdidasToFuhsunMapping.Count; j++)
{
((Excel.Range)workSheet.Columns[j]).AutoFit();
}
StepProgressBar();
#endregion
#region 2nd sheet
//2nd sheet
Excel._Worksheet newWorksheet = (Excel.Worksheet)excelApp.Worksheets.Add();
newWorksheet.Name = "Summary";
#region make header/mapping
var summaryHeader = Models.SummaryHeaders;
var summaryMapping = Models.SummaryMapping;
if (shipping)
{
summaryHeader.Add("Shipping\nInstruction");
summaryMapping.Add("AW");
}
if (priority)
{
summaryHeader.Add("Priority &\nOrder Type");
summaryMapping.Add("CF");
}
if (remark)
{
summaryHeader.Add("Remarks");
summaryMapping.Add("CJ");
}
if (manufacturer)
{
summaryHeader.Add("Actual\nManufacturer");
summaryMapping.Add("AC");
}
if (material)
{
summaryHeader.Add("Supplier\nMaterial");
summaryMapping.Add("BA");
}
if (ad[0])
{
summaryHeader.Add("Additional\nOptional 1");
summaryMapping.Add("CK");
}
if (ad[1])
{
summaryHeader.Add("Additional\nOptional 2");
summaryMapping.Add("CL");
}
if (ad[2])
{
summaryHeader.Add("Additional\nOptional 3");
summaryMapping.Add("CM");
}
if (ad[3])
{
summaryHeader.Add("Additional\nOptional 4");
summaryMapping.Add("CN");
}
if (ad[4])
{
summaryHeader.Add("Additional\nOptional 5");
summaryMapping.Add("CO");
}
//add issue Date 20181130
if (issueDate)
{
summaryHeader.Insert(0, "Issue Date");
summaryMapping.Insert(0, "A");
}
//add contact person
summaryHeader.Add("Contact Person");
summaryMapping.Add("O");
//move remark to checkbox
//summaryHeader.AddRange(ExcelTool.RemarkHeaders);
//summaryMapping.AddRange(ExcelTool.RemarkMapping);
#endregion
//add header
for (int i = 0; i < summaryHeader.Count; i++)
{
newWorksheet.Cells[1, 1 + i] = summaryHeader[i];
}
//add hidden column on summary
var hiddenColumn = new List <int>();
//fill content
for (var i = 0; i < Dt.Rows.Count; i++)
{
if (string.IsNullOrEmpty(Dt.Rows[i].Field <string>(1)) || string.IsNullOrEmpty(Dt.Rows[i].Field <string>(2)))
{
continue;
}
for (var j = 0; j < summaryMapping.Count; j++)
{
var source = summaryMapping[j];
if (string.IsNullOrEmpty(source))
{
continue;
}
var value = Dt.Rows[i].Field <string>(ExcelTool.LettersToInt(source) - 1);
//unit formatting
if (source == "BP" && value == "YARD")
{
value = "YRD";
}
if (source == "CJ")
{
value = Dt.Rows[i].Field <string>(ExcelTool.LettersToInt("AU") - 1) + value;
}
//contact user
if (source == "O")
{
value = Regex.Match(value, @"[a-zA-Z0-9.]*").Value;
hiddenColumn.Add(j + 1);
}
//matrial color => color code/desc
if (source == "BM" && !string.IsNullOrEmpty(value))
{
var ar = value.Split(new[] { ';', '/' }, StringSplitOptions.RemoveEmptyEntries);
var colorCode = new List <string>();
var colorDesc = new List <string>();
foreach (string t in ar)
{
var s = t.Trim();
if (s.Length > 5)
{
colorCode.Add(s.Substring(s.Length - 4, 4));
colorDesc.Add(s.Substring(0, s.Length - 4).Trim());
}
//only multi colorCode
else
{
colorCode.Add(t);
}
}
//try make sure the format is right
if (colorCode.Count > 0 && colorCode.Count == colorDesc.Count)
{
value = string.Join("/", colorCode);
newWorksheet.Cells[2 + i, "F"] = string.Join("/", colorDesc);
}
//only code
else
{
value = string.Join("/", colorCode);
}
}
newWorksheet.Cells[2 + i, j + 1] = value;
}
//for YTI batch field
try
{
var last = summaryHeader.Count;
//put batch column's value to last-1
var v = Dt.Rows[i].Field <string>(ExcelTool.LettersToInt("CQ") - 1);
if (!string.IsNullOrEmpty(v))
{
var range = newWorksheet.Cells[2 + i, last - 1] as Excel.Range;
if (range != null && range.Value2 != null)
{
newWorksheet.Cells[2 + i, last - 1] = range.Value2.ToString() + "," + v;
}
else
{
newWorksheet.Cells[2 + i, last - 1] = v;
}
}
}
catch (Exception)
{
//do nothing
//Console.WriteLine(ex.Message);
}
StepProgressBar();
}
//fit columns
for (var j = 1; j <= Models.SummaryHeaders.Count; j++)
{
((Excel.Range)newWorksheet.Columns[j]).AutoFit();
}
//set summary row height
for (var i = 2; i <= Dt.Rows.Count + 1; i++)
{
((Excel.Range)workSheet.Rows[i]).RowHeight = 16 * lineHeight;
((Excel.Range)newWorksheet.Rows[i]).RowHeight = 16 * lineHeight;
}
//hidden column
for (int i = 0; i < hiddenColumn.Count; i++)
{
((Excel.Range)newWorksheet.Columns[hiddenColumn[i]]).Hidden = true;
}
StepProgressBar();
#endregion
workSheet.SaveAs(location);
//close
//excelApp.Workbooks.Close();
workbooks.Close();
excelApp.Quit();
//wait release
while (Marshal.ReleaseComObject(excelApp) != 0)
{
}
while (Marshal.ReleaseComObject(workbooks) != 0)
{
}
//excelApp = null;
//workbooks = null;
}
