Hi, currently I am using closedxml.excel to create my excel workbook with multiple excel sheets, however it also helps to create "Table Format" function in each excel sheet at the same time, which restricted me to copy or move multiple excel sheets to another new excel workbook at a time.
In order to copy and move multiple excel sheets to another excel sheet at a time, I have to create the excel workbook without any formatting in the data table.
Any help is appreciated, thank you!
using (XLWorkbook wb = new XLWorkbook())
{
string FileName = fromYear.ToString() + fromMonth.ToString() + "-" + toYear.ToString() + toMonth.ToString() + "(" + cbattr.ToString() + ")" + ".xlsx";
if (FileName.Contains("cc.[AssetClsname]"))
{
FileName = FileName.Replace("cc.[AssetClsname]", "AssetClsname");
}
if (FileName.Contains("cc.[AssetClsname_IMA]"))
{
FileName = FileName.Replace("cc.[AssetClsname_IMA]", "AssetClsname_IMA");
}
if (FileName.Contains(","))
{
FileName = FileName.Replace(",", " ");
foreach (DataTable dt in ds.Tables)
{
var ws = wb.Worksheets.Add(dt.TableName);
ws.Cell(1, 1).InsertTable(dt);
ws.Columns().AdjustToContents();
ws.Tables.FirstOrDefault().ShowAutoFilter = false;
ws.Clear(XLClearOptions.Formats);
}
p++;
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}