How to remove table format in exported excel in ASP.Net

Last Reply on Jun 29, 2016 08:37 PM By beekee

Posted on Jun 27, 2016 03:26 AM

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();
        }
 
    }
}
You are viewing reply posted by: beekee on Jun 29, 2016 08:37 PM.
Posted on Jun 29, 2016 08:37 PM Modified on on Jun 29, 2016 08:38 PM

Hi, the solution to remove the table format is ..

foreach (DataTable dt in ds.Tables)
{
    var ws = wb.Worksheets.Add(dt.TableName);            
    ws.Cell(1, 1).InsertData(dt.Rows);
    ws.Columns().AdjustToContents();
}

Hope it does help some of you :)