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