// GET: api/Excel public void Get() { XLWorkbook Workbook = new XLWorkbook(@"c:\temp\Sample.xlsx"); IXLWorksheet Worksheet = Workbook.Worksheets.First(); Debug.WriteLine(Worksheet); int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber(); IXLCell CellForNewData = Worksheet.Cell(8, 1); DataTable datatable = new DataTable(); datatable.Columns.Add("Name"); datatable.Columns.Add("Marks"); datatable.Columns.Add("Rank"); var i = 10; while (i > 0) { DataRow dr = datatable.NewRow(); dr["Name"] = "shakthi"; dr["Marks"] = "shiva"; dr["Rank"] = "sharan"; datatable.Rows.Add(dr); --i; } CellForNewData.InsertTable(datatable.AsEnumerable()); Worksheet.Row(8).Delete(); Workbook.Save(); }
public void DoTest_GenerateThenAttach() { var gdt = new GenerateDataTable(); var table = gdt.GetNewTable(1); Stopwatch sw = new Stopwatch(); sw.Start(); var filePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") + ".xlsx"); int looRowCount = 8000; using (var workbook = new XLWorkbook()) { workbook.Worksheets.Add(gdt.GetTableWithNRows(table, looRowCount), table.TableName); workbook.SaveAs(filePath); } int i = 2; int loop = 300000 / looRowCount; while (i <= loop) { using (var workbook = new XLWorkbook(filePath)) { IXLWorksheet Worksheet = workbook.Worksheet(table.TableName); int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber(); IXLCell CellForNewData = Worksheet.Cell(NumberOfLastRow + 1, 1); CellForNewData.InsertTable(gdt.GetTableWithNRows(table, looRowCount)); if (i == loop) { Worksheet.Columns().AdjustToContents(); } workbook.Save(); Console.WriteLine($"Loop {i} work done..."); i++; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } sw.Stop(); Console.WriteLine($"Save all rows used {sw.ElapsedMilliseconds} ms"); }
private HttpResponseMessage writeToExcel(DataTable dt) { XLWorkbook Workbook = new XLWorkbook(@"c:\temp\Sample.xlsx"); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); IXLWorksheet Worksheet = Workbook.Worksheets.First(); Debug.WriteLine(Worksheet); IXLCell cellfornewdata = Worksheet.Cell(8, 1); cellfornewdata.InsertTable(dt.AsEnumerable()); Worksheet.Row(8).Delete(); Workbook.SaveAs(@"c:\temp\Sample.xlsx"); try { Workbook workbook = new Workbook(); workbook.LoadFromFile(@"c:\temp\Sample.xlsx"); workbook.ConverterSetting.SheetFitToPage = true; workbook.CustomDocumentProperties.Add("_MarkAsFinal", true); workbook.CustomDocumentProperties.Add("The Editor", "E-iceblue"); workbook.CustomDocumentProperties.Add("Phone number1", 81705109); workbook.CustomDocumentProperties.Add("Revision number", 7.12); workbook.CustomDocumentProperties.Add("Revision date", DateTime.Now); workbook.SaveToFile(@"c:\temp\Sample.pdf", FileFormat.PDF); Process.Start(@"c:\temp\Sample.pdf"); var pdf = @"c:\temp\Sample.pdf"; var stream = new FileStream(pdf, FileMode.Open, FileAccess.Read); result.Content = new StreamContent(stream); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf"); return(result); } catch (Exception ex) { Debug.WriteLine("Ëxception is thrown"); Debug.WriteLine(ex.Message); return(result); } }