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); }