public void Create(string filePath) { string tempFile = ExampleHelper.GetTempFilePath(filePath); try { new BasicTable().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); // Get a range object var rngHeaders = ws.Range("B3:F3"); // Insert some rows/columns before the range ws.Row(1).InsertRowsAbove(2); ws.Column(1).InsertColumnsBefore(2); // Change the background color of the headers rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon; ws.Columns().AdjustToContents(); workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
public static void Execute(DownloadServiceOptions options) { using (var workbook = new XLWorkbook(options.File)) using (var worksheet = workbook.Worksheet(1)) { var fullRange = worksheet.RangeUsed(); var rangeUsed = worksheet.Range(2, 1, fullRange.RangeAddress.LastAddress.RowNumber, fullRange.RangeAddress.LastAddress.ColumnNumber); const int movieNameCell = 1; const int yearCell = 2; const int processedCell = 3; const int downloadedCell = 4; var fullCount = 0; foreach (var row in rangeUsed.RowsUsed()) { if (fullCount > 100) break; // must have 4 cells, and the processed cell must not contain an x if (row.CellCount() == 4 && !row.Cell(processedCell).GetValue<string>().Equals("x")) { var urls = CreateUrl(options.BaseUrl, row.Cell(movieNameCell).GetValue<string>(), row.Cell(yearCell).GetValue<string>()); var downloaded = false; foreach (var url in urls) { Thread.Sleep(1000 * 30); // wait 30 seconds var hash = GetTorrentMagnetHash(PullSource(url)); if (string.IsNullOrEmpty(hash)) continue; var torrent = string.Format("http://torcache.net/torrent/{0}.torrent", hash); var result = DownloadTorrent(torrent, options.DownloadPath); if (!result) continue; downloaded = true; break; } row.Cell(processedCell).SetValue("x"); if (downloaded) row.Cell(downloadedCell).SetValue("x"); fullCount++; } } workbook.Save(); } }
//var wb = new XLWorkbook(northwinddataXlsx); public ExcelHelper(string filePath) { this.filePath = filePath; workbook = new XLWorkbook(filePath); if(workbook.Worksheets.Count>0) worksheet = workbook.Worksheet(1); }
//public static void Create() //{ // ////create new xls file // //string file = "C:\\newdoc.xls"; // ////Workbook workbook = new Workbook(); // ////Worksheet worksheet = new Worksheet("First Sheet"); // ////worksheet.Cells[0, 1] = new Cell((short)1); // ////worksheet.Cells[2, 0] = new Cell(9999999); // ////worksheet.Cells[3, 3] = new Cell((decimal)3.45); // ////worksheet.Cells[2, 2] = new Cell("Text string"); // ////worksheet.Cells[2, 4] = new Cell("Second string"); // ////worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00"); // ////worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD"); // ////worksheet.Cells.ColumnWidth[0, 1] = 3000; // ////workbook.Worksheets.Add(worksheet); // ////workbook.Save(file); // ////// open xls file // ////Workbook book = Workbook.Load(file); // ////Worksheet sheet = book.Worksheets[0]; // //var wb = new XLWorkbook(); // //var ws = wb.Worksheets.Add("Contacts"); // //// Title // //ws.Cell("B2").Value = "Contacts"; // //// First Names // //ws.Cell("B3").Value = "FName"; // //ws.Cell("B4").Value = "John"; // //ws.Cell("B5").Value = "Hank"; // //ws.Cell("B6").Value = "Dagny"; // //// Last Names // //ws.Cell("C3").Value = "LName"; // //ws.Cell("C4").Value = "Galt"; // //ws.Cell("C5").Value = "Rearden"; // //ws.Cell("C6").Value = "Taggart"; // //// Boolean // //ws.Cell("D3").Value = "Outcast"; // //ws.Cell("D4").Value = true; // //ws.Cell("D5").Value = false; // //ws.Cell("D6").Value = false; // //// DateTime // //ws.Cell("E3").Value = "DOB"; // //ws.Cell("E4").Value = new DateTime(1919, 1, 21); // //ws.Cell("E5").Value = new DateTime(1907, 3, 4); // //ws.Cell("E6").Value = new DateTime(1921, 12, 15); // //// Numeric // //ws.Cell("F3").Value = "Income"; // //ws.Cell("F4").Value = 2000; // //ws.Cell("F5").Value = 40000; // //ws.Cell("F6").Value = 10000; // //// From worksheet // //var rngTable = ws.Range("B2:F6"); // //// From another range // //var rngDates = rngTable.Range("D3:D5"); // The address is relative to rngTable (NOT the worksheet) // //var rngNumbers = rngTable.Range("E3:E5"); // The address is relative to rngTable (NOT the worksheet) // //// Using OpenXML's predefined formats // //rngDates.Style.NumberFormat.NumberFormatId = 15; // //// Using a custom format // //rngNumbers.Style.NumberFormat.Format = "$ #,##0"; // //var rngHeaders = rngTable.Range("A2:E2"); // The address is relative to rngTable (NOT the worksheet) // //rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // //rngHeaders.Style.Font.Bold = true; // //rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua; // //rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin; // //rngTable.Cell(1, 1).Style.Font.Bold = true; // //rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue; // //rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // //rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge() // ////Add a thick outside border // //rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; // //// You can also specify the border for each side with: // //// rngTable.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick; // //// rngTable.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick; // //// rngTable.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick; // //// rngTable.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; // //ws.Columns(2, 6).AdjustToContents(); // //wb.SaveAs("BasicTable.xlsx"); // //CreateResult(); //} public static void CreateResult(ExcelModel model, bool needToSave = true) { model.SetMeltsOxides(); Model = model; var disk = AppDomain.CurrentDomain.BaseDirectory.ToCharArray().First(); string fileName = string.Format(@"{1}:\result{0}.xlsx", DateTime.Now.ToString("dd-MM-yy_hhmm"), disk); string template = "template.xlsx"; var wb = new XLWorkbook(template); var ws = wb.Worksheet("result"); CreateHeader(ws); SetRows(ws); SetData(ws); wb.SaveAs(fileName); if (needToSave) { Sqlite.SaveProject(Model); } Model = null; }
public void Test() { XLWorkbook wb = new XLWorkbook("D:\\aaa.xlsx"); wb.Worksheet("Sheet1").Cell(1, 5).Value = 3; wb.Worksheet("Sheet1").Cell(2, 5).Value = "BBB"; wb.Worksheet("Sheet1").Cell(3, 5).SetValue("ASDASDASDASD"); //wb.AddWorksheet("CCC"); wb.SaveAs("D:\\bbb.xlsx"); //wb.Save(); }
public void InsertingSheets3() { var wb = new XLWorkbook(); wb.Worksheets.Add("Sheet3"); wb.Worksheets.Add("Sheet2", 1); wb.Worksheets.Add("Sheet1", 1); Assert.AreEqual("Sheet1", wb.Worksheet(1).Name); Assert.AreEqual("Sheet2", wb.Worksheet(2).Name); Assert.AreEqual("Sheet3", wb.Worksheet(3).Name); }
public void DeletingSheets1() { var wb = new XLWorkbook(); wb.Worksheets.Add("Sheet3"); wb.Worksheets.Add("Sheet2"); wb.Worksheets.Add("Sheet1", 1); wb.Worksheet("Sheet3").Delete(); Assert.AreEqual("Sheet1", wb.Worksheet(1).Name); Assert.AreEqual("Sheet2", wb.Worksheet(2).Name); Assert.AreEqual(2, wb.Worksheets.Count); }
public void Create(string filePath) { var tempFile = ExampleHelper.GetTempFilePath(filePath); try { new BasicTable().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); // Define a range with the data var firstTableCell = ws.FirstCellUsed(); var lastTableCell = ws.LastCellUsed(); var rngData = ws.Range(firstTableCell.Address, lastTableCell.Address); // Copy the table to another worksheet var wsCopy = workbook.Worksheets.Add("Contacts Copy"); wsCopy.Cell(1, 1).Value = rngData; workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
public void Create(string filePath) { string tempFile1 = ExampleHelper.GetTempFilePath(filePath); string tempFile2 = ExampleHelper.GetTempFilePath(filePath); try { new UsingTables().Create(tempFile1); var wb = new XLWorkbook(tempFile1); var wsSource = wb.Worksheet(1); // Copy the worksheet to a new sheet in this workbook wsSource.CopyTo("Copy"); // We're going to open another workbook to show that you can // copy a sheet from one workbook to another: new BasicTable().Create(tempFile2); var wbSource = new XLWorkbook(tempFile2); wbSource.Worksheet(1).CopyTo(wb, "Copy From Other"); // Save the workbook with the 2 copies wb.SaveAs(filePath); } finally { if (File.Exists(tempFile1)) { File.Delete(tempFile1); } if (File.Exists(tempFile2)) { File.Delete(tempFile2); } } }
public void GivenCreateCalled_AndDateDataRows_WhenFillData_ThenDatePrecisionToSecondsPreserved() { var nowWithSecondPrecision = DateTime.Now.Truncate(TimeSpan.FromSeconds(1)); var data = new List<List<object>> { new List<object> { nowWithSecondPrecision }, new List<object> { nowWithSecondPrecision.AddDays(2) }, new List<object> { nowWithSecondPrecision.TimeOfDay } }; string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx"); byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx"); using (MemoryStream stream = new MemoryStream()) { stream.Write(templateData, 0, (int)templateData.Length); using (var target = new StudentProfileExportFile()) { target.Create(stream); target.FillData(data); } File.WriteAllBytes(outputPath, stream.ToArray()); } using (XLWorkbook workbook = new XLWorkbook(outputPath)) { IXLWorksheet worksheet = workbook.Worksheet(1); var date1 = worksheet.Cell("A3").GetDateTime(); var date2 = worksheet.Cell("A4").GetDateTime(); var timeSpan = worksheet.Cell("A5").GetTimeSpan(); Assert.AreEqual(data[0][0], date1); Assert.AreEqual(data[1][0], date2); Assert.AreEqual(data[2][0], timeSpan); } }
//---------------------------------------------------------// // Method //---------------------------------------------------------// public void ExcelFile() { string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx"; Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait) try { using (oWBook = new XLWorkbook(fileName)) { // 編集 oWSheet = oWBook.Worksheet(1); // シートを開く editReviewData(pubDat); // 保存 oWBook.SaveAs(tempFile); // Excel保存 } } catch (Exception ex) { MessageBox.Show(ex.Message); Cursor.Current = Cursors.Default; // マウスカーソルを戻す return; } Cursor.Current = Cursors.Default; // マウスカーソルを戻す System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel }
private void findProvderName() { Point providerLocation = new Point(0, 0); workbook = workbooks[0]; var sheet = workbook.Worksheet(1); var colRange = sheet.Range("A:A"); foreach (var cell in colRange.CellsUsed()) { if (cell.Value != null) { String value = (String)cell.Value; int cellRow = cell.Address.RowNumber; if (value.Contains(provider)) { providerLocation = new Point(1, cellRow); for (int metricNumber = 0; metricNumber < 8; metricNumber++) { setMetricDataLocations(providerLocation, metricNumber); } break; } } } }
private void findProvderName() { Point providerLocation = new Point(0, 0); //int fileNumber = 0; for (int fileNumber = 0; fileNumber < workbooks.Count; fileNumber++) { workbook = workbooks[fileNumber]; var sheet = workbook.Worksheet(1); var colRange = sheet.Range("A:A"); foreach (var cell in colRange.CellsUsed()) { if (cell.Value != null) { String value = (String)cell.Value; int cellRow = cell.Address.RowNumber; if (value.Contains(provider)) { providerLocation = new Point(1, cellRow); setMetricDataLocations(providerLocation, fileNumber); break; } } } } //metrics.Insert(0, now.Month +"-" + now.Year); }
public TranslationData Read(string srcPath) { // ワークブックの作成 using (var workbook = new XLWorkbook(srcPath)) { // ワークブック指定の名前のシートを取得 var worksheet = workbook.Worksheet(ExcelConverter.SheetName); var dataRange = worksheet.Range(LanguageTitleRow, DataColumn, worksheet.LastRowUsed().RowNumber(), worksheet.LastColumnUsed().ColumnNumber()); var dataTable = dataRange.AsTable(); var langList = dataTable.DataRange.Columns() .Select(o => new { Locale = o.Cell(0).GetString(), Values = o.Cells().Select(i => new LocalizationItem(worksheet.Cell(i.Address.RowNumber, OffsetColumn).GetString(), worksheet.Cell(i.Address.RowNumber, OffsetColumn + 1).GetString(), i.GetString())) }); var result = langList.Select(o => new LanguageData(o.Locale, o.Values)); return new TranslationData(result); } }
//---------------------------------------------------------------------- // SubRoutine //---------------------------------------------------------------------- private void editExcelSheet(string sheetName, TaskData td, TaskNoteData tnd, TaskIndData[] tid, PartnersData pd, TaskOp tod) { string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx"; Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait) using (oWBook = new XLWorkbook(FileName)) { // シートを開く //oWSheet = oWBook.Worksheet(sheetName); oWSheet = oWBook.Worksheet(1); // 編集 //MessageBox.Show("Excel書込み開始"); editTaskDataPart(td); editTaskNoteDataPart(tnd); editTaskIndDataPart(tid); editPartnersDataPart(pd); editPersonsDataPart(tod); // 保存 oWBook.SaveAs(tempFile); // Excel保存 } Cursor.Current = Cursors.Default; // マウスカーソルを戻す System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel //if (File.Exists(tempFile)) File.Delete(tempFile); }
public void Create(string filePath) { string tempFile = ExampleHelper.GetTempFilePath(filePath); try { new BasicTable().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); var rngTable = ws.Range("B2:F6"); rngTable.Transpose(XLTransposeOptions.MoveCells); ws.Columns().AdjustToContents(); workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
internal static void VerifyExcel(MemoryStream stream, [CallerMemberName] string?caller = null) { stream.Seek(0, SeekOrigin.Begin); //File.WriteAllBytes(caller + ".xlsx", stream.ToArray()); var book = new ClosedXML.Excel.XLWorkbook(stream); VerifyExcel(book.Worksheet(1), caller); }
public static XLS.XLWorkbook getClosedXMLWorkbook() { XLS.XLWorkbook workbook = new XLS.XLWorkbook(@"C:\Users\fleet\Documents\cruises.xlsx"); var worksheet = workbook.Worksheet(1); var usedRange = worksheet.RangeUsed(); Console.WriteLine("USED: {0}", usedRange.RowCount()); return(workbook); }
public void Create(string filePath) { var wb = new XLWorkbook(); foreach (var wsNum in Enumerable.Range(1, 5)) { wb.Worksheets.Add("Original Pos. is " + wsNum.ToString()); } // Move first worksheet to the last position wb.Worksheet(1).Position = wb.Worksheets.Count() + 1; // Delete worksheet on position 4 (in this case it's where original position = 5) wb.Worksheet(4).Delete(); // Swap sheets in positions 1 and 2 wb.Worksheet(2).Position = 1; wb.SaveAs(filePath); }
private static void DuplicateExcelSheetByEPPlus(string baseDir, string bookPath) { var outPath = Path.Combine(baseDir, "Book1 - ClosedXML.xlsx"); using (var book = new ClosedXML.Excel.XLWorkbook(bookPath)) { book.Worksheet("Sheet1").CopyTo("Sheet2"); book.SaveAs(outPath); } }
static void Main(string[] args) { XLWorkbook workbook = new XLWorkbook("table_prefecturecode.xlsx"); IXLWorksheet worksheet = workbook.Worksheet(1); int lastRow = worksheet.LastRowUsed().RowNumber(); for (int i = 1; i <= lastRow; i++) { IXLCell cell = worksheet.Cell(i, 2); Console.WriteLine(cell.Value); } }
static void Main(string[] args) { var fileSpese = args[0]; var fileAnalisi = args[1]; var template = args[2]; new GeneraDaTemplate().Run(fileSpese, fileAnalisi, template); return; var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2013; var anni = Enumerable.Range(2013, numeroDiAnniFinoAdOggi + 1); using (var analisiWb = new XLWorkbook()) { var dati = analisiWb.Worksheets.Add("Dati"); int riga = 1; dati.Cell(riga, "A").Value = "Data"; dati.Cell(riga, "B").Value = "Categoria"; dati.Cell(riga, "C").Value = "Descrizione"; dati.Cell(riga, "D").Value = "Spesa"; riga++; using (var spese = new XLWorkbook(fileSpese)) { foreach (var anno in anni) { var wsAnno = spese.Worksheet(anno + ""); var lastRowNumber = wsAnno.LastRowUsed().FirstCell().Address.RowNumber; var laura = wsAnno.Range("B1", "E"+ lastRowNumber).Rows().Select(Movimento.TryParse2017); var valerio = wsAnno.Range("G1", "J" + lastRowNumber).Rows().Select(Movimento.TryParse2017); var comune = wsAnno.Range("L1", "O" + lastRowNumber).Rows().Select(Movimento.TryParse2017); var movimenti = laura.Concat(valerio).Concat(comune).Where(r => r != null).ToArray(); foreach (var movimento in movimenti) { dati.Cell(riga, "A").Value = movimento.Data; dati.Cell(riga, "B").Value = movimento.Categoria; dati.Cell(riga, "C").Value = movimento.Descrizione; dati.Cell(riga, "D").Value = movimento.Spesa; riga++; } } var dataAsTable = dati.RangeUsed().AsTable(); dati.Tables.Add(dataAsTable); } analisiWb.SaveAs(fileAnalisi); } }
// Wakamatsu // Wakamatsu 20170301 //private string editExcelSheet(string sheetName, DataTable dt, FormatSet[] FormatSet) private string editExcelSheet(string FileName, string sheetName, DataTable dt, FormatSet[] FormatSet) { // Wakamatsu 20170301 //string tempFile = Folder.MyDocuments() + @"\" + sheetName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string tempFile = Folder.MyDocuments() + @"\" + FileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; // Wakamatsu 20170301 Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait) if (System.IO.File.Exists(fileName) == false) { Cursor.Current = Cursors.Default; // マウスカーソルを戻す return("× テンプレートファイルが存在しません。\r\n"); } // Wakamatsu 20170322 //using(oWBook = new XLWorkbook(fileName)) //{ try { // Wakamatsu 20170322 using (oWBook = new XLWorkbook(fileName)) { oWSheet = oWBook.Worksheet(sheetName); // Excelファイル出力 if (MasterExport(dt, FormatSet) == true) { oWBook.SaveAs(tempFile); // Excel保存 Cursor.Current = Cursors.Default; // マウスカーソルを戻す return("○ Excel出力が正常に終了しました。\r\n" + " " + dt.Rows.Count + "件のデータを出力しました。\r\n"); } else { Cursor.Current = Cursors.Default; // マウスカーソルを戻す return("× Excel出力ができませんでした。\r\n"); } } } catch (Exception ex) { Cursor.Current = Cursors.Default; // マウスカーソルを戻す // Wakamatsu 20170322 //MessageBox.Show(ex.Message); //return "× Excel出力ができませんでした。\r\n"; return(ex.Message + "\r\n× Excel出力ができませんでした。\r\n"); // Wakamatsu 20170322 } //} }
public void Create(string filePath) { string tempFile = ExampleHelper.GetTempFilePath(filePath); try { new BasicTable().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); // Define a range with the data var firstDataCell = ws.Cell("B4"); var lastDataCell = ws.LastCellUsed(); var rngData = ws.Range(firstDataCell.Address, lastDataCell.Address); // Delete all rows where Outcast = false (the 3rd column) rngData.Rows() // From all rows .Where(r => !r.Cell(3).GetBoolean()) // where the 3rd cell of each row is false .ForEach(r => r.Delete()); // delete the row and shift the cells up (the default for rows in a range) //// Put a light gray background to all text cells //rngData.Cells() // From all cells // .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text // .ForEach(c => c.Style.Fill.BackgroundColor = XLColor.LightGray); // Fill with a light gray var cells = rngData.Cells(); var filtered = cells.Where(c => c.DataType == XLCellValues.Text); var list = filtered.ToList(); foreach (var c in list) { c.Style.Fill.BackgroundColor = XLColor.LightGray; } // Put a thick border to the bottom of the table (we may have deleted the bottom cells with the border) rngData.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
static void Main(string[] args) { var workbook = new XLWorkbook("Input.xlsx"); var ws = workbook.Worksheet("Posts"); var cell = ws.Column(3).FirstCellUsed().CellBelow(); while (!cell.IsEmpty()) { int value = cell.GetValue<int>(); Console.WriteLine("Value: " + value); cell.CellRight().Style.Fill.BackgroundColor = (value < 0 ? XLColor.Red : XLColor.Green); cell = cell.CellBelow(); } workbook.SaveAs("Output.xlsx"); Console.WriteLine("Press any key to continue..."); Console.ReadKey(); }
public static IList <CruiseData> getCruisedataFromExcel() { IList <CruiseData> cruisedataList = new List <CruiseData>(); XLS.XLWorkbook workbook = new XLS.XLWorkbook(@"C:\Users\fleet\Documents\cruises.xlsx"); var worksheet = workbook.Worksheet(1); var usedRange = worksheet.RangeUsed(); for (int i = 1; i <= usedRange.RowCount(); i++) { CruiseData cs = new CruiseData(); cs.cruise = worksheet.Row(i).Cell(1).GetValue <String>(); cs.ship = worksheet.Row(i).Cell(2).GetValue <String>(); cs.price = worksheet.Row(i).Cell(3).GetValue <String>(); cruisedataList.Add(cs); } workbook.Dispose(); return(cruisedataList); }
public void Create(string filePath) { string tempFile = ExampleHelper.GetTempFilePath(filePath); try { new MergeCells().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); ws.Range("B1:F1").InsertRowsBelow(1); ws.Range("A3:A9").InsertColumnsAfter(1); ws.Row(1).Delete(); ws.Column(1).Delete(); ws.Range("E8:E9").InsertColumnsAfter(1); ws.Range("F2:F8").Merge(); ws.Range("E3:E4").InsertColumnsAfter(1); ws.Range("F2:F8").Merge(); ws.Range("E1:E2").InsertColumnsAfter(1); ws.Range("G2:G8").Merge(); ws.Range("E1:E2").Delete(XLShiftDeletedCells.ShiftCellsLeft); ws.Range("D3:E3").InsertRowsBelow(1); ws.Range("A1:B1").InsertRowsBelow(1); ws.Range("B3:D3").Merge(); ws.Range("A1:B1").Delete(XLShiftDeletedCells.ShiftCellsUp); ws.Range("B8:D8").Merge(); ws.Range("D8:D9").Clear(); workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
public List<String> GetMetricsNames(string dashboardFile, int sheetNum) { List<String> metricsNames = new List<string>(); try { _dashboard = new XLWorkbook(dashboardFile); //iterate thorugh all the dashboard sheets, and try to pull the metrics name row int numSheets = _dashboard.Worksheets.Count; var sheet = _dashboard.Worksheet(sheetNum); metricsNames = ReturnMetricsRow(sheet); } catch (Exception ex) { Console.WriteLine(ex.Message); } return metricsNames; }
public static List<IXLCells> ReadFromExcelFile(string fileName, string workSheetName, string leadCell) { var rowCellList = new List<IXLCells>(); var wb = new XLWorkbook(fileName); var ws = wb.Worksheet(workSheetName); // Look for the first row used var firstRowUsed = ws.FirstRowUsed(); int rowCount = 0; // Narrow down the row so that it only includes the used part var categoryRow = firstRowUsed.RowUsed(); while (!categoryRow.Cell(1).IsEmpty()) { string categoryName = categoryRow.Cell(1).GetString(); Console.WriteLine(categoryName + "\n" + categoryRow.Cells().Count()); rowCellList.Add(categoryRow.Cells()); categoryRow = categoryRow.RowBelow(); rowCount++; } return rowCellList; }
public void Create(string filePath) { string tempFile = ExampleHelper.GetTempFilePath(filePath); try { new BasicTable().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); // Change the background color of the headers var rngHeaders = ws.Range("B3:F3"); rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon; // Change the date formats var rngDates = ws.Range("E4:E6"); rngDates.Style.DateFormat.Format = "MM/dd/yyyy"; // Change the income values to text var rngNumbers = ws.Range("F4:F6"); foreach (var cell in rngNumbers.Cells()) { string formattedString = cell.GetFormattedString(); cell.DataType = XLCellValues.Text; cell.Value = formattedString + " Dollars"; } ws.Columns().AdjustToContents(); workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
public void Create(string filePath) { string tempFile = ExampleHelper.GetTempFilePath(filePath); try { new BasicTable().Create(tempFile); var workbook = new XLWorkbook(tempFile); var ws = workbook.Worksheet(1); var rngTable = ws.Range("B2:F6"); rngTable.Row(rngTable.RowCount() - 1).Delete(XLShiftDeletedCells.ShiftCellsUp); // Place some markers var cellNextRow = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber + 1, rngTable.RangeAddress.LastAddress.ColumnNumber); cellNextRow.Value = "ColumnRight Row"; var cellNextColumn = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber, rngTable.RangeAddress.LastAddress.ColumnNumber + 1); cellNextColumn.Value = "ColumnRight Column"; rngTable.Transpose(XLTransposeOptions.MoveCells); rngTable.Transpose(XLTransposeOptions.MoveCells); rngTable.Transpose(XLTransposeOptions.ReplaceCells); rngTable.Transpose(XLTransposeOptions.ReplaceCells); ws.Columns().AdjustToContents(); workbook.SaveAs(filePath); } finally { if (File.Exists(tempFile)) { File.Delete(tempFile); } } }
public void GivenCreateCalled_AndHeaderTextProvided_WhenSetupColumnHeaders_ThenSavedFileContainsAddedHeaderCellWithFormatting() { string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx"); byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx"); using (MemoryStream stream = new MemoryStream()) { stream.Write(templateData, 0, (int)templateData.Length); using (var target = new StudentProfileExportFile()) { target.Create(stream); target.SetupColumnHeaders(new[] { "whatever", "to be formatted" }); } File.WriteAllBytes(outputPath, stream.ToArray()); } using (XLWorkbook workbook = new XLWorkbook(outputPath)) { IXLWorksheet worksheet = workbook.Worksheet(1); IXLCell preexistingHeaderCell = worksheet.Cell(2, "A"); IXLCell actualAddedHeaderCell = worksheet.Cell(2, "B"); Assert.AreEqual(preexistingHeaderCell.Style, actualAddedHeaderCell.Style); } }
public void Run(string fileSpese, string fileAnalisi, string template) { var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2013; var anni = Enumerable.Range(2013, numeroDiAnniFinoAdOggi + 1); File.Copy(template, fileAnalisi, true); using (var analisiWb = new XLWorkbook(fileAnalisi)) { var dati = analisiWb.Worksheet("Dati"); int riga = 2; using (var spese = new XLWorkbook(fileSpese)) { foreach (var anno in anni) { var wsAnno = spese.Worksheet(anno + ""); var lastRowNumber = wsAnno.LastRowUsed().FirstCell().Address.RowNumber; if (anno < 2017) { riga = Estrai_Template_2013(wsAnno, lastRowNumber, dati, riga); } if (anno >= 2017) { riga = Estrai_Template_2017(wsAnno, lastRowNumber, dati, riga); } } var dataAsTable = dati.RangeUsed().AsTable(); dati.Tables.Add(dataAsTable); } analisiWb.SaveAs(fileAnalisi); } }
public StampaPagamento() { _wb = new ClosedXML.Excel.XLWorkbook(Path.Combine(Application.StartupPath , @"TemplateExcel\ProtoPagamento.xlsx")); _ws = _wb.Worksheet(1); }
private void Export_Novy() { if (!InvokeRequired) { form.timer_ClearInfo.Stop(); } else { this.BeginInvoke(new Action(() => form.timer_ClearInfo.Stop())); } form.infoBox.Text = form.jazyk.Message_Exportuji; form.Update(); DateTime start; DateTime konec; over = false; List <ExportRow> exportRadky = new List <ExportRow>(); int odec = 0; if (DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == DayOfWeek.Sunday && (int)DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == 0) { odec = 1; } if (radioButton3.Checked) { start = dateTimePicker1.Value; konec = dateTimePicker2.Value; } else if (radioButton1.Checked) { konec = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); //tohle zrevidovat, jestli by nestačilo jen to Add day v catch bez try try { start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day + odec); } catch { start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day); start = start.AddDays(odec); } } else { start = DateTime.Now.AddDays(-7); start = start.AddDays(-(int)(start.DayOfWeek - 1)); start = new DateTime(start.Year, start.Month, start.Day); konec = new DateTime(start.Year, start.Month, start.Day).AddDays(6); } //Projít tickety splňující podmínku a zařadit je správně do exportu for (DateTime d = start; d <= konec; d = d.AddDays(1)) { if (form.poDnech.ContainsKey(d)) { foreach (string s in form.poDnech[d].Keys) { foreach (Ticket t in form.poDnech[d][s]) { DateTime pauzaDohromady = new DateTime(); DateTime cas = new DateTime(); DateTime hrubyCas = new DateTime(); for (int i = 0; i < t.PauzyDo.Count; i++) { if (t.PauzyDo[i].ToString("H:mm") != "0:00") { pauzaDohromady = pauzaDohromady.AddHours(t.PauzyDo[i].Hour - t.PauzyOd[i].Hour).AddMinutes(t.PauzyDo[i].Minute - t.PauzyOd[i].Minute); } } try { cas = cas.AddHours((t.Do.Hour - t.Od.Hour) - pauzaDohromady.Hour).AddMinutes((t.Do.Minute - t.Od.Minute) - pauzaDohromady.Minute); hrubyCas = form.RoundUp(cas, TimeSpan.FromMinutes(30)); //dny[d][t.TerpT].Add(t, hrubyCas); string den = ""; switch (d.DayOfWeek) { case DayOfWeek.Monday: den = "Pondělí"; break; case DayOfWeek.Tuesday: den = "Úterý"; break; case DayOfWeek.Wednesday: den = "Středa"; break; case DayOfWeek.Thursday: den = "Čtvrtek"; break; case DayOfWeek.Friday: den = "Pátek"; break; case DayOfWeek.Saturday: den = "Sobota"; break; case DayOfWeek.Sunday: den = "Neděle"; break; } ExportTyp et; if (t.TypPrace == 0 || t.TypPrace == 2 || t.TypPrace == 8 || t.TypPrace == 12 || t.TypPrace == 16 || t.TypPrace == 20 || t.TypPrace == 24) { et = ExportTyp.Normal; } else if (t.TypPrace == 1 || t.TypPrace == 6 || t.TypPrace == 10 || t.TypPrace == 15 || t.TypPrace == 18 || t.TypPrace == 23 || t.TypPrace == 27) { et = ExportTyp.Holiday; } else if (t.TypPrace == 3 || t.TypPrace == 5 || t.TypPrace == 9 || t.TypPrace == 13 || t.TypPrace == 17 || t.TypPrace == 21 || t.TypPrace == 25) { et = ExportTyp.Prescas; } else if (t.TypPrace == 4 || t.TypPrace == 7 || t.TypPrace == 11 || t.TypPrace == 14 || t.TypPrace == 19 || t.TypPrace == 22 || t.TypPrace == 26) { et = ExportTyp.Compens; } else { et = ExportTyp.Normal; } if (exportRadky.Count == 0) { exportRadky.Add(new ExportRow()); } List <int> toSkip = new List <int>(); if (t.CustomTerp == "") { t.CustomTerp = Zakaznici.GetTerp(t.Zakaznik); } //když není task, tak defaultně incident 1.2.1 - nová verze by neměla umět uložit bez tasku if (t.CustomTask == "") { t.CustomTask = Zakaznici.Terpy.Get <NbtCompound>("Task").Get <NbtString>("Incident").Value; } //přiřazení ticketu ke správnému řádku a dni for (int i = 0; i < exportRadky.Count; i++) { if (exportRadky[i].Terp == null) { exportRadky[i].Terp = t.CustomTerp; exportRadky[i].Task = t.CustomTask; exportRadky[i].Typ = et; exportRadky[i].Radek[den].Koment = t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n"; decimal tCas = hrubyCas.Hour; if (hrubyCas.Minute == 30) { tCas += 0.5m; } exportRadky[i].Radek[den].Cas = tCas; break; } else if (exportRadky[i].Terp == t.CustomTerp && exportRadky[i].Task == t.CustomTask && exportRadky[i].Typ == et) { if ((exportRadky[i].Radek[den].Koment.Length + (t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n").Length < 240)) { exportRadky[i].Radek[den].Koment += t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n"; decimal tCas = hrubyCas.Hour; if (hrubyCas.Minute == 30) { tCas += 0.5m; } exportRadky[i].Radek[den].Cas += tCas; break; } else { exportRadky.Add(new ExportRow()); continue; } } else if (i < exportRadky.Count - 1) { continue; } exportRadky.Add(new ExportRow()); } } catch { MessageBox.Show(form.jazyk.Windows_Export_Ticket + " " + t.ID + " - " + t.Zakaznik + ", " + form.jazyk.Windows_Export_NaKteremJsiPracoval + " " + t.Datum.ToString("d.MM.yyyy") + ", " + form.jazyk.Windows_Export_Neukoncen); } } } } } //přepočet času na 8h //celkový čas normálních ticketů (statní se neupravují) Dictionary <string, decimal> casy = new Dictionary <string, decimal> { { "Pondělí", 0 }, { "Úterý", 0 }, { "Středa", 0 }, { "Čtvrtek", 0 }, { "Pátek", 0 }, { "Sobota", 0 }, { "Neděle", 0 } }; foreach (ExportRow s in exportRadky) { if (s.Typ == ExportTyp.Normal) { casy["Pondělí"] += s.Radek["Pondělí"].Cas; casy["Úterý"] += s.Radek["Úterý"].Cas; casy["Středa"] += s.Radek["Středa"].Cas; casy["Čtvrtek"] += s.Radek["Čtvrtek"].Cas; casy["Pátek"] += s.Radek["Pátek"].Cas; casy["Sobota"] += s.Radek["Sobota"].Cas; casy["Neděle"] += s.Radek["Neděle"].Cas; } } //výběr řádků, co se upraví čas foreach (string cs in casy.Keys) { if (casy[cs] == 8 || casy[cs] == 0) { continue; } else if (casy[cs] < 8) { Dictionary <int, decimal> pridat = new Dictionary <int, decimal>(); for (int i = 0; i < exportRadky.Count; i++) { if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0) { pridat.Add(i, exportRadky[i].Radek[cs].Cas); } } pridat = pridat.OrderBy(x => x.Value).ToDictionary(x => x.Key, x => x.Value); decimal zbyva = 8 - casy[cs]; decimal prumerNaRadek = Math.Ceiling((zbyva / pridat.Count) / 0.5m) * 0.5m; //úprava času int index = 0; for (decimal d = zbyva; d > 0; d -= prumerNaRadek) { if (d - prumerNaRadek < 0) { prumerNaRadek = d; } pridat[pridat.Keys.ElementAt(index)] += prumerNaRadek; index++; zbyva -= prumerNaRadek; } foreach (int newI in pridat.Keys) { exportRadky[newI].Radek[cs].Cas = pridat[newI]; } } else if (casy[cs] > 8) { Dictionary <int, decimal> ubrat = new Dictionary <int, decimal>(); for (int i = 0; i < exportRadky.Count; i++) { if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0.5m) { ubrat.Add(i, exportRadky[i].Radek[cs].Cas); } } ubrat = ubrat.OrderByDescending(x => x.Value).ToDictionary(x => x.Key, x => x.Value); decimal zbyva = casy[cs] - 8; decimal prumerNaRadek = Math.Ceiling((zbyva / ubrat.Count) / 0.5m) * 0.5m; //úprava času int index = 0; for (decimal d = zbyva; d > 0; d -= prumerNaRadek) { if (d - prumerNaRadek < 0) { prumerNaRadek = d; } ubrat[ubrat.Keys.ElementAt(index)] -= prumerNaRadek; index++; zbyva -= prumerNaRadek; } foreach (int newI in ubrat.Keys) { exportRadky[newI].Radek[cs].Cas = ubrat[newI]; } } } //export do souboru //pro excel File.WriteAllBytes(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx", Properties.Resources.mytime_template); Excel.XLWorkbook export = new Excel.XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx"); Excel.IXLWorksheet exportSheet = export.Worksheet(1); NumberFormatInfo nfi = new NumberFormatInfo(); nfi.NumberDecimalSeparator = "."; int row = 2; foreach (ExportRow s in exportRadky) { if (s.Terp != null) { //project exportSheet.Cell(row, 1).Value = s.Terp; //project name exportSheet.Cell(row, 2).Value = "najdiSiSam"; //task exportSheet.Cell(row, 3).SetValue(s.Task); //task name exportSheet.Cell(row, 4).Value = "TyVisCo"; //type exportSheet.Cell(row, 5).Value = s.GetTyp(); //pondělí (čas, comment) exportSheet.Cell(row, 6).Value = s.Radek["Pondělí"].Cas.ToString() == "0" ? "" : s.Radek["Pondělí"].Cas.ToString(nfi); exportSheet.Cell(row, 7).Value = s.Radek["Pondělí"].Koment.Replace("\t", " ").Replace("\"", ""); //úterý exportSheet.Cell(row, 10).Value = s.Radek["Úterý"].Cas.ToString() == "0" ? "" : s.Radek["Úterý"].Cas.ToString(nfi); exportSheet.Cell(row, 11).Value = s.Radek["Úterý"].Koment.Replace("\t", " ").Replace("\"", ""); //středa exportSheet.Cell(row, 14).Value = s.Radek["Středa"].Cas.ToString() == "0" ? "" : s.Radek["Středa"].Cas.ToString(nfi); exportSheet.Cell(row, 15).Value = s.Radek["Středa"].Koment.Replace("\t", " ").Replace("\"", ""); //čtvrtek exportSheet.Cell(row, 18).Value = s.Radek["Čtvrtek"].Cas.ToString() == "0" ? "" : s.Radek["Čtvrtek"].Cas.ToString(nfi); exportSheet.Cell(row, 19).Value = s.Radek["Čtvrtek"].Koment.Replace("\t", " ").Replace("\"", ""); //pátek exportSheet.Cell(row, 22).Value = s.Radek["Pátek"].Cas.ToString() == "0" ? "" : s.Radek["Pátek"].Cas.ToString(nfi); exportSheet.Cell(row, 23).Value = s.Radek["Pátek"].Koment.Replace("\t", " ").Replace("\"", ""); //sobota exportSheet.Cell(row, 26).Value = s.Radek["Sobota"].Cas.ToString() == "0" ? "" : s.Radek["Sobota"].Cas.ToString(nfi); exportSheet.Cell(row, 27).Value = s.Radek["Sobota"].Koment.Replace("\t", " ").Replace("\"", ""); //neděle exportSheet.Cell(row, 30).Value = s.Radek["Neděle"].Cas.ToString() == "0" ? "" : s.Radek["Neděle"].Cas.ToString(nfi); exportSheet.Cell(row, 31).Value = s.Radek["Neděle"].Koment.Replace("\t", " ").Replace("\"", ""); row++; } } export.Save(); export.Dispose(); form.infoBox.Text = ""; saveFileDialog1.AddExtension = true; saveFileDialog1.DefaultExt = "xlsx"; saveFileDialog1.Filter = "Excel|*.xlsx"; saveFileDialog1.FileName = "MyTime Info.xlsx"; saveFileDialog1.ShowDialog(); }
// Wakamatsu //---------------------------------------------------------------------- // SubRoutine //---------------------------------------------------------------------- private void editExcelSheet(string sheetName, PublishData pd, DataGridView dgv, DataGridView dgv1, DataGridView dgv2) { string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx"; Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait) using (oWBook = new XLWorkbook(fileName)) { oWSheet = oWBook.Worksheet(sheetName); switch (sheetName) { case "EstimateTop": editEstimateTop(pd); oWSheet = oWBook.Worksheet("EstimateCont"); editEstimateCont(dgv); break; case "EstimateCopy": editEstimateCopy(pd); oWSheet = oWBook.Worksheet("EstimateCont"); editEstimateCont(dgv); break; case "Planning": editPlanning(pd); break; case "PlanningCont": editPlanningCont(pd, dgv); break; case "OsOrder": editOutsourceOrder(pd); editOutsourceConfirm(pd); break; case "OsConfirm": editOutsourceConfirm(pd); break; case "OsContent": editOutsourceContent(pd, dgv); break; case "OsARegular": editAccountsRegular(pd, dgv, dgv1, dgv2); break; case "OsAContract": editAccountsContract(pd, dgv); break; case "Invoice": editAccountsInvoice(pd, dgv); break; case "VolumeInvoice": editAccountsVolumeInvoice(pd, dgv); break; // Wakamatsu 20170302 case "TaskSummary": editTaskSummary(pd, dgv); break; // Wakamatsu 20170302 default: break; } oWBook.SaveAs(tempFile); // Excel保存 } Cursor.Current = Cursors.Default; // マウスカーソルを戻す System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel }
//---------------------------------------------------------/ // Method //---------------------------------------------------------/ public void ExcelFile() { string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx"; Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait) // Wakamatsu 20170315 try { using (oWBook = new XLWorkbook(fileName)) { if (tla == null || tla.Length == 0) { DMessage.DataNotExistence("中断します!"); return; } // 編集 oWSheet = oWBook.Worksheet(1); // シートを開く // Wakamatsu 20170315 //readyExcelRows(tla.Length, 5); readyExcelRows(tla.Length, 4); int sNo = 5; for (int i = 0; i < tla.Length; i++) { // Wakamatsu 20170315 using (IXLRange SetRange = oWSheet.Range("A5:I5")) // テンプレートデータ行コピー/ペースト SetRange.CopyTo(oWSheet.Cell(sNo + i, 1)); // Wakamatsu 20170315 if (i == 0) { officeName = tla[i].OfficeName; departName = tla[i].DepartName; // Wakamatsu 20170315 //oWSheet.Cell(1, 7).Value = DateTime.Today; oWSheet.Cell(2, 2).Value = DateTime.Today; oWSheet.Cell(3, 2).Value = officeName + " " + departName; // Wakamatsu 20170315 } oWSheet.Cell(sNo + i, 1).Value = tla[i].TaskCode; oWSheet.Cell(sNo + i, 2).Value = tla[i].TaskName; oWSheet.Cell(sNo + i, 3).Value = tla[i].PartnerName; oWSheet.Cell(sNo + i, 4).Value = tla[i].Contract; oWSheet.Cell(sNo + i, 5).Value = tla[i].StartDate; oWSheet.Cell(sNo + i, 6).Value = tla[i].EndDate; oWSheet.Cell(sNo + i, 7).Value = tla[i].SalesM; oWSheet.Cell(sNo + i, 8).Value = tla[i].LeaderM; oWSheet.Cell(sNo + i, 9).Value = tla[i].IssueDate; // Wakamatsu 20170315 if (i != 0) { oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair; } if (i == tla.Length - 1) { oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.BottomBorder = XLBorderStyleValues.Thin; } else { oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.BottomBorder = XLBorderStyleValues.Hair; } // Wakamatsu 20170315 } } // 保存 oWBook.SaveAs(tempFile); // Excel保存 } // Wakamatsu 20170315 catch (Exception ex) { MessageBox.Show(ex.Message); Cursor.Current = Cursors.Default; // マウスカーソルを戻す return; } // Wakamatsu 20170315 Cursor.Current = Cursors.Default; // マウスカーソルを戻す //System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel // pdf file 出力 DateTime now = DateTime.Now; outputFile = System.IO.Path.GetDirectoryName(tempFile) + @"\業務一覧表_" + officeName + "_" + departName + "_" + now.ToString("yyMMddHHmmss"); PublishExcelToPdf etp = new PublishExcelToPdf(); etp.ExcelToPDF(tempFile, outputFile); if (File.Exists(tempFile)) { File.Delete(tempFile); } }
private void button_Click(object sender, EventArgs e) { if (initProc) { return; } Button btn = (Button)sender; switch (btn.Name) { case "buttonOpen": // Wakamatsu 20170301 //fileName = Files.Open("M_Office.xlsx", Folder.MyDocuments(), "xlsx"); fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx"); // Wakamatsu 20170301 if (fileName == null) { textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n"); } else { textBoxMsg.AppendText("☆ " + fileName + "の内容で事業所マスタを登録・更新します。\r\n"); } break; case "buttonCancel": // Wakamatsu 20170323 fileName = null; textBoxMsg.Text = ""; break; case "buttonStart": if (fileName == null) { // Wakamatsu 20170323 textBoxMsg.AppendText("× 取り込むファイルを指定してください。\r\n"); return; } MasterMaintOp mmo = new MasterMaintOp(); int[] procArray = new int[] { 0, 0 }; switch (System.IO.Path.GetExtension(fileName)) { case ".xlsx": // Wakamatsu 20170227 try { oWBook = new XLWorkbook(fileName); procArray = mmo.MaintOfficeByExcelData(oWBook.Worksheet(1)); // Wakamatsu 20170227 if (procArray[0] < 0) { textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n"); return; } // Wakamatsu 20170227 } // Wakamatsu 20170227 catch (Exception ex) { textBoxMsg.AppendText(ex.Message + "\r\n"); textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n"); return; } // Wakamatsu 20170227 break; default: procArray[0] = -1; textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n"); break; } if (procArray[0] < 0) { return; } textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n"); textBoxMsg.AppendText(procArray[0] + "件のデータを登録しました。\r\n"); textBoxMsg.AppendText(procArray[1] + "件のデータを更新しました。\r\n"); break; case "buttonEnd": this.Close(); break; case "buttonExport": textBoxMsg.AppendText("☆ 処理を開始しました。\r\n"); string SetSQL = ""; SetSQL += "OfficeCode, OfficeName, MemberCode, MemberName, "; SetSQL += "Title, PostCode, Address, TelNo, FaxNo, OrderSeqNo, "; SetSQL += "OrderLastNo, PurchaseSeqNo "; SetSQL += "FROM M_Office "; SetSQL += "ORDER BY OfficeID"; SqlHandling sqlh = new SqlHandling(); // SQL実行クラス // レコードを取得する DataTable dt = sqlh.SelectFullDescription(SetSQL); if (dt == null) { textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n"); return; } // フォーマット設定用構造体 PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count]; // フォーマット設定 FormatSetting(ref FormatSet); // Excel出力クラス // Wakamatsu 20170301 //PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate("M_Office.xlsx")); PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName)); // Wakamatsu 20170301 // Excelファイル出力 // Wakamatsu 20170301 //textBoxMsg.AppendText(publ.ExcelFile("M_Office", dt, FormatSet)); textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet)); // Wakamatsu 20170301 break; default: break; } }
private void button_Click(object sender, EventArgs e) { if (initProc) { return; } Button btn = ( Button )sender; switch (btn.Name) { case "buttonOpen": fileName = Files.Open(bookName, Folder.MyDocuments(), "xlsx"); if (fileName == null) { textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n"); } else { textBoxMsg.AppendText("☆ " + fileName + "の内容を作業項目マスタに書き込みます。\r\n"); } break; case "buttonCancel": textBoxMsg.Text = ""; break; case "buttonStart": if (fileName == null) { return; } MasterMaintOp mmo = new MasterMaintOp(); if (!mmo.MWorkItems_Delete(hp.MemberCode)) { textBoxMsg.AppendText("× 旧データの削除に失敗しました処理を中断します。\r\n"); return; } mmo = new MasterMaintOp(); int procCount = 0; switch (System.IO.Path.GetExtension(fileName)) { case ".csv": procCount = mmo.MaintWorkItemsByCSVData(fileName, hp.MemberCode); break; case ".xlsx": oWBook = new XLWorkbook(fileName); procCount = mmo.MaintWorkItemsByExcelData(oWBook.Worksheet(1), hp.MemberCode); break; default: procCount = -1; textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n"); break; } if (procCount < 0) { return; } textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n"); textBoxMsg.AppendText(procCount + "件のデータを登録しました。\r\n"); break; case "buttonExport": textBoxMsg.AppendText("☆ 処理を開始しました。\r\n"); //string SetSQL = "ItemCode, UItem, Item, ItemDetail, Unit, StdCost, MemberCode, UpdateDate" // + " FROM M_WorkItems WHERE MemberCode = '" + hp.MemberCode + "'"; string SetSQL = "ItemCode, UItem, Item, ItemDetail, Unit, StdCost, MemberCode, UpdateDate" + " FROM M_WorkItems WHERE MemberCode = "; SqlHandling sqlh = new SqlHandling(); // SQL実行クラス // レコードを取得する DataTable dt = sqlh.SelectFullDescription(SetSQL + "'" + hp.MemberCode + "'"); if (dt == null) { dt = sqlh.SelectFullDescription(SetSQL + "'000'"); if (dt == null) { textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n"); return; } else { textBoxMsg.AppendText("△ " + hp.MemberName + "様の作業項目マスタが未登録のため、共用の作業項目マスタをExcel出力します。\r\n"); } } // フォーマット設定用構造体 PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count]; // フォーマット設定 FormatSetting(ref FormatSet); // Excel出力クラス PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(bookName)); // Excelファイル出力 textBoxMsg.AppendText(publ.ExcelFile(masterName, sheetName, dt, FormatSet)); break; case "buttonEnd": this.Close(); break; default: break; } }
private void button_Click(object sender, EventArgs e) { if (initProc) { return; } Button btn = (Button)sender; switch (btn.Name) { case "buttonOpen": // Wakamatsu 20170301 //fileName = Files.Open("M_Members.xlsx",Folder.MyDocuments(),"xlsx"); fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx"); // Wakamatsu 20170301 if (fileName == null) { textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n"); } else { textBoxMsg.AppendText("☆ " + fileName + "の内容で社員マスタを登録・更新します。\r\n"); } break; case "buttonCancel": // Wakamatsu 20170323 fileName = null; textBoxMsg.Text = ""; break; case "buttonStart": if (fileName == null) { // Wakamatsu 20170323 textBoxMsg.AppendText("× 取り込むファイルを指定してください。\r\n"); return; } MasterMaintOp mmo = new MasterMaintOp(); int[] procArray = new int[] { 0, 0 }; switch (System.IO.Path.GetExtension(fileName)) { case ".xlsx": // Wakamatsu 20170227 try { oWBook = new XLWorkbook(fileName); procArray = mmo.MaintMembersByExcelData(oWBook.Worksheet(1)); // Wakamatsu 20170227 if (procArray[0] < 0) { textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n"); return; } // Wakamatsu 20170227 } // Wakamatsu 20170227 catch (Exception ex) { textBoxMsg.AppendText(ex.Message + "\r\n"); textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n"); return; } // Wakamatsu 20170227 break; default: procArray[0] = -1; textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n"); break; } if (procArray[0] < 0) { return; } textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n"); textBoxMsg.AppendText(procArray[0] + "件のデータを登録しました。\r\n"); textBoxMsg.AppendText(procArray[1] + "件のデータを更新しました。\r\n"); break; case "buttonExport": textBoxMsg.AppendText("☆ 処理を開始しました。\r\n"); string SetSQL = ""; SetSQL += "MemberCode, Name, Phonetic, OfficeCode + Department, "; SetSQL += "BirthDate, PostCode, Address, PostCode2, Address2, TelNo, "; SetSQL += "CellularNo, CellularNo2, EMail, MobileEMail, BloodType, "; SetSQL += "JoinDate, FinalEducation, GradDate, BasicPNo, HealthInsNo, "; SetSQL += "EmploymentInsNo, GainQDate, BankName, BBranchName, AccountType, "; SetSQL += "AccountNo, EContact, RadiationMedical, MedicalCheckup, "; SetSQL += "FormWage, MemberType, AccessLevel, Enrollment, Note "; SetSQL += "FROM M_Members "; SetSQL += "ORDER BY RIGHT('0000' + MemberCode,4)"; SqlHandling sqlh = new SqlHandling(); // SQL実行クラス // レコードを取得する DataTable dt = sqlh.SelectFullDescription(SetSQL); if (dt == null) { textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n"); return; } // フォーマット設定用構造体 PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count]; // フォーマット設定 FormatSetting(ref FormatSet); // Excel出力クラス // Wakamatsu 20170301 //PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate("M_Members.xlsx")); PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName)); // Wakamatsu 20170301 // Excelファイル出力 // Wakamatsu 20170301 //textBoxMsg.AppendText(publ.ExcelFile("M_Members",dt,FormatSet)); textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet)); // Wakamatsu 20170301 break; case "buttonEnd": this.Close(); break; default: break; } }
private void button_Click(object sender, EventArgs e) { if (initProc) { return; } Button btn = (Button)sender; switch (btn.Name) { case "buttonOpen": // Wakamatsu 20170301 //fileName = Files.Open("M_Cost.xlsx", Folder.MyDocuments(), "xlsx"); fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx"); // Wakamatsu 20170301 if (fileName == null) { textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n"); } else { textBoxMsg.AppendText("☆ " + fileName + "の内容を原価情報マスタに書き込みます。\r\n"); } break; case "buttonCancel": // Wakamatsu 20170323 fileName = null; textBoxMsg.Text = ""; break; case "buttonStart": if (fileName == null) { // Wakamatsu 20170323 textBoxMsg.AppendText("× 取り込むファイルを指定してください。\r\n"); return; } MasterMaintOp mmo = new MasterMaintOp(); /* * if (!mmo.AllMCost_Delete()) * { * labelMsg.Text += "旧データの削除に失敗しました処理を中断します。\r\n"; * return; * } * mmo = new MasterMaintOp(); */ int[] procCount = new int[] { 0, 0 }; switch (System.IO.Path.GetExtension(fileName)) { //case ".csv": //procCount = mmo.MaintCostByCSVData(fileName); //break; case ".xlsx": // Wakamatsu 20170227 try { oWBook = new XLWorkbook(fileName); procCount = mmo.MaintCostByExcelData(oWBook.Worksheet(1)); // Wakamatsu 20170227 if (procCount[0] < 0) { textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n"); return; } // Wakamatsu 20170227 } // Wakamatsu 20170227 catch (Exception ex) { textBoxMsg.AppendText(ex.Message + "\r\n"); textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n"); return; } // Wakamatsu 20170227 break; default: textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n"); break; } if (procCount[0] < 0) { return; } textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n"); textBoxMsg.AppendText(procCount[0] + "件のデータを登録しました。\r\n"); textBoxMsg.AppendText(procCount[1] + "件のデータを更新しました。\r\n"); break; // Wakamatsu case "buttonExport": textBoxMsg.AppendText("☆ 処理を開始しました。\r\n"); string SetSQL = ""; // Wakamatsu 20170303 SqlHandling sqlh = new SqlHandling(); // SQL実行クラス SetSQL += "OfficeCode, OfficeName "; SetSQL += "FROM M_Office "; SetSQL += "ORDER BY OfficeID"; DataTable dt = sqlh.SelectFullDescription(SetSQL); if (dt == null) { textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n"); return; } PrintOut.Publish.FormatSet[] FormatSet = null; // フォーマット設定用構造体 PrintOut.Publish publ = null; // Excel出力クラス for (int i = 0; i < dt.Rows.Count; i++) { SetSQL = ""; SetSQL += "CostCode, Item, ItemDetail, Unit, Cost, "; SetSQL += "OfficeCode, MemberCode "; SetSQL += "FROM M_Cost "; SetSQL += "WHERE OfficeCode = '" + Convert.ToString(dt.Rows[i][0]) + "' "; SetSQL += "ORDER BY CostCode"; DataTable dt1 = sqlh.SelectFullDescription(SetSQL); if (dt1 != null) { FormatSet = new PrintOut.Publish.FormatSet[dt1.Columns.Count]; // フォーマット設定 FormatSetting(ref FormatSet); publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName)); // Excelファイル出力 textBoxMsg.AppendText(publ.ExcelFile(masterName + "(" + Convert.ToString(dt.Rows[i][1]) + ")", SheetName, dt1, FormatSet)); } } //SetSQL += "CST.CostCode, CST.Item, CST.ItemDetail, CST.Unit, CST.Cost, "; //SetSQL += "CST.OfficeCode, CST.MemberCode "; //SetSQL += "FROM M_Cost AS CST "; //SetSQL += "LEFT JOIN M_Office AS OFC "; //SetSQL += "ON CST.OfficeCode = OFC.OfficeCode "; //SetSQL += "ORDER BY OFC.OfficeID, CST.CostCode"; //SqlHandling sqlh = new SqlHandling(); // SQL実行クラス //// レコードを取得する //DataTable dt = sqlh.SelectFullDescription(SetSQL); //if (dt == null) //{ // textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n"); // return; //} //// フォーマット設定用構造体 //PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count]; //// フォーマット設定 //FormatSetting(ref FormatSet); //// Excel出力クラス //// Wakamatsu 20170301 ////PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate("M_Cost.xlsx")); //PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName)); //// Wakamatsu 20170301 //// Excelファイル出力 //// Wakamatsu 20170301 ////textBoxMsg.AppendText(publ.ExcelFile("M_Cost", dt, FormatSet)); //textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet)); //// Wakamatsu 20170301 // Wakamatsu 20170303 break; // Wakamatsu case "buttonEnd": this.Close(); break; default: break; } }
public void _openExcel(String dashboardFile, String metricsFolder) { //grab all the files in the metrics folder metrics_files = Directory.GetFiles(metricsFolder); if (File.Exists("Copy.xlsx")) { File.Delete("Copy.xlsx"); } File.Copy(dashboardFile, "Copy.xlsx"); //try openeing the excel sheets listed in metricsFile try { _dashboard = new XLWorkbook(dashboardFile); //iterate thorugh all the dashboard sheets, prep them for new daata by adding new rows, then extrract data from //metrics reports and insert into those sheets int numSheets = _dashboard.Worksheets.Count; for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++) { var sheet = _dashboard.Worksheet(sheetNum); prepDashboardSheets(sheet); } _dashboard.Save(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public void Run(string fileSpese, string fileAnalisi) { var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2011; var anni = Enumerable.Range(2011, numeroDiAnniFinoAdOggi + 1); using (var analisiWb = new XLWorkbook()) { var dati = analisiWb.Worksheets.Add("Dati"); int riga = 1; dati.Cell(riga, "A").Value = "Data"; dati.Cell(riga, "B").Value = "Categoria"; dati.Cell(riga, "C").Value = "Descrizione"; dati.Cell(riga, "D").Value = "Spesa"; riga++; using (var spese = new XLWorkbook(fileSpese)) { foreach (var anno in anni) { var wsAnno = spese.Worksheet(anno + ""); var movimenti = wsAnno.Rows().Select(Movimento.TryParse).Where(r => r != null).ToArray(); foreach (var movimento in movimenti) { dati.Cell(riga, "A").Value = movimento.Data; dati.Cell(riga, "B").Value = movimento.Categoria; dati.Cell(riga, "C").Value = movimento.Descrizione; dati.Cell(riga, "D").Value = movimento.Spesa; riga++; } } var dataAsTable = dati.RangeUsed().AsTable(); dati.Tables.Add(dataAsTable); var range = dati.Range("B1", "B30"); //range = worksheet.RangeUsed(); var pivotSh = analisiWb.Worksheets.Add("Pivot"); } analisiWb.SaveAs(fileAnalisi); } }
public ActionResult MyOrders(string cname, string cid) { cname = HttpUtility.UrlDecode(cname); cid = HttpUtility.UrlDecode(cid); var wb = new XLWorkbook(Server.MapPath("~/App_Data/进度表.xlsm")); var ws = wb.Worksheet("进度单"); var firstRowUsed = ws.FirstRowUsed(); var categoryRow = firstRowUsed.RowUsed(); //categoryRow = categoryRow.RowBelow(); var firstPossibleAddress = ws.Row(categoryRow.RowNumber()).FirstCell().Address; var lastPossibleAddress = ws.LastCellUsed().Address; var companyRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed(); var companyTable = companyRange.AsTable(); var retValue = companyTable.DataRange.Rows() .Where(companyRow => companyRow.Field(0).GetString() == cname && companyRow.Field(1).GetString() == cid) .Select(companyRow => new Order() { customerName = companyRow.Field(0).GetString(), customerID = companyRow.Field(1).GetString(), orderNO = companyRow.Field(2).GetString(), dateBlanking = ReadCellValueByDateTime(companyRow, 3), dateAccessary = ReadCellValueByDateTime(companyRow, 4), dateCombination = ReadCellValueByDateTime(companyRow, 5), datePressureMachine = ReadCellValueByDateTime(companyRow, 6), dateCarving = ReadCellValueByDateTime(companyRow, 7), dateCutOut = ReadCellValueByDateTime(companyRow, 8), dateModeling = ReadCellValueByDateTime(companyRow, 9), dateAttachedWorkers = ReadCellValueByDateTime(companyRow, 10), datePolished = ReadCellValueByDateTime(companyRow, 11), dateStockTaking = ReadCellValueByDateTime(companyRow, 12), datePutty = ReadCellValueByDateTime(companyRow, 13), dateFinishes = ReadCellValueByDateTime(companyRow, 14), datePrimer = ReadCellValueByDateTime(companyRow, 15), dateSecondaryGrinding = ReadCellValueByDateTime(companyRow, 16), dateFacePaint = ReadCellValueByDateTime(companyRow, 17), datePacking = ReadCellValueByDateTime(companyRow, 18) }) .OrderByDescending(o=>o.orderNO) .ToList(); return new JsonNetActionResult(retValue); }
/// <summary> /// ConvertXLToCSV /// </summary> /// <param name="data"></param> /// <returns></returns> private static byte[] ConvertXLToCSV(byte[] data) { var workbook = new XLWorkbook(new System.IO.MemoryStream(data)); var worksheet = workbook.Worksheet(1); StringBuilder csvStringBuilder = new StringBuilder(); // Look for the first row used var firstRowUsed = worksheet.FirstRowUsed(); // Narrow down the row so that it only includes the used part var exportedRow = firstRowUsed.RowUsed(); int iCount = exportedRow.CellCount(); //Verify null value for current column while (!IsBlankRow(exportedRow, iCount)) { RenderRow(exportedRow, ref csvStringBuilder, iCount); csvStringBuilder.Append("\r\n"); exportedRow = exportedRow.RowBelow(); } return Encoding.UTF8.GetBytes(csvStringBuilder.ToString()); }