private void Run_Click_Click(object sender, EventArgs e)
{
if (sSelectedFolder != string.Empty && sSelectedFile != string.Empty)
{
Microsoft.Office.Interop.Excel.Application excelApp;
string fnTarget;
DateTime date;
string numOfPcs;
string clientCode;
string awb;
string awbEnding;
//fnTarget = "\\DPD TRACKING " + numOfPcs + " pcs_" + clientCode + "_" + awbEnding + ".xlsx";
//string fileTarget = "C:\\Users\\SBL-Warehouse\\Desktop\\ExcelBatchSave_Project" + fnTarget;
string fileTemplate = Application.StartupPath + "\\DPD TRACKING 123 pcs_SOSO_7777_template.xlsx";
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wbTarget;
Microsoft.Office.Interop.Excel.Worksheet sh;
string fn;
SpreadsheetLight.SLDocument ss;
fn = sSelectedFile;
ss = new SpreadsheetLight.SLDocument(fn);
ss.SelectWorksheet("Sheet1");
//int rt;
//rt = 0;
Int32 row_count;
row_count = ss.GetWorksheetStatistics().EndRowIndex;
for (int ri = 2; ri <= row_count; ri++)
{
date = ss.GetCellValueAsDateTime(ri, 1);
clientCode = ss.GetCellValueAsString(ri, 2);
awb = ss.GetCellValueAsString(ri, 3);
awbEnding = awb.Substring(awb.Length - 4);
numOfPcs = ss.GetCellValueAsString(ri, 4);
wbTarget = excelApp.Workbooks.Open(fileTemplate);
sh = wbTarget.Worksheets["Sheet1"];
sh.Cells[1, 1] = date;
sh.Cells[1, 2] = numOfPcs + " pcs";
sh.Cells[1, 5] = awb;
fnTarget = "\\DPD TRACKING " + numOfPcs + " pcs_" + clientCode + "_" + awbEnding + ".xlsx";
string fileTarget = sSelectedFolder + fnTarget;
wbTarget.SaveAs(fileTarget);
wbTarget.Close(true);
excelApp.Quit();
}
string promptValue = Prompt.ShowDialog("Program finished successfully!", "Confirmation");
}
}
private static byte[] InMemoryDocumentToBytes(SpreadsheetLight.SLDocument document)
{
Debug.Assert(document != null);
using (var stream = new MemoryStream())
{
document.SaveAs(stream);
return(stream.ToArray());
}
}
private static void DeleteFirstWorksheet(SpreadsheetLight.SLDocument document)
{
Debug.Assert(document != null);
// If we have just one worksheet then test was empty (!) or something went wrong,
// just do nothing and let caller manage an erroneous situation (if any)
var worksheets = document.GetWorksheetNames();
if (worksheets.Count > 1)
{
document.DeleteWorksheet(worksheets[0]);
}
}
private static void WriteMeasuresForSingleTest(SpreadsheetLight.SLDocument document, int columnIndex, BenchmarkedMethod method)
{
Debug.Assert(document != null);
Debug.Assert(columnIndex > 0);
Debug.Assert(method != null);
int rowIndex = 1;
document.SetCellValue(rowIndex++, columnIndex, method.Name);
foreach (var measure in method.Measures)
{
document.SetCellValue(rowIndex++, columnIndex, measure.TotalMilliseconds);
}
}
private static void AddWorksheetForBenchmark(SpreadsheetLight.SLDocument document, Benchmark benchmark)
{
Debug.Assert(document != null);
Debug.Assert(benchmark != null);
// Each benchmark has its own worksheet
document.AddWorksheet(benchmark.Name);
// First row is test name and rows under it are measures, each column is a separate test
int columnIndex = 1;
foreach (var method in benchmark.Methods)
{
WriteMeasuresForSingleTest(document, columnIndex++, method);
}
}
private static SpreadsheetLight.SLDocument CreateAndFillSpreadsheet(IEnumerable <Benchmark> benchmarks)
{
Debug.Assert(benchmarks != null);
// NOTE: this renderer ignores Statistic property content, it may even be null because
// here we only export raw data.
var document = new SpreadsheetLight.SLDocument();
foreach (var benchmark in benchmarks)
{
AddWorksheetForBenchmark(document, benchmark);
}
// SL can't change name of current worksheet then we left first default one empty and we delete it now.
DeleteFirstWorksheet(document);
return(document);
}
