Tuesday, November 18, 2014

Convert List as Excel Sheet



1. Include Closed XML & DocumentFormat.OpenXml  dll in the project. (refer this https://closedxml.codeplex.com/documentation for dll)

2. Create an instance of workbook -> Var excel =new XLWorkbook();

3. Add sheets to excel workbook -> var excelsheet = excel.Worksheets.Add(“sheetname”);

4. Create rows in excel sheet -> Row(1).Style.Font.Bold = true;

5. Add column to excel sheet -> var column1 = Worksheets.Column("A");

6. Add cells to excel sheet -> excelsheet.Cell(int row, int column).Value = “value";

7. Add Author & Title to excel if necessary

8. Clear all headers and content output from the current response

9. Get or Set the HTTP MIME type of the current response

10.Add an HTTP header to the current response  for compatibility with earlier versions of ASP

11.Save excel into memory stream & Writes the entire contents to it. Then Close the current  memory stream and release any resources. Send all currently buffered output to the client


sample:

   var ws = wb.Worksheets.Add(sheetName);    ws.Row(1).Style.Font.Bold = true;
   var col1 = ws.Column("A");
   var col2 = ws.Column("B");
   var col3 = ws.Column("C");
   var col4 = ws.Column("D");
   col1.Width = 30;
   col2.Width = 10;
   col3.Width = 15;
   col4.Width = 10;

   var i = 1;
   foreach (var exceldata in _exceldatalst)
   {
        ws.Cell(i, 1).Value = exceldata.aaa != null && !string.IsNullOrEmpty(exceldata.aaa) ? exceldata.aaa : "";
        ws.Cell(i, 2).Value = exceldata.bbb != null && !string.IsNullOrEmpty(exceldata.bbb) ? exceldata.bbb : "";
        ws.Cell(i, 3).Value = exceldata.ccc != null && !string.IsNullOrEmpty(exceldata.ccc) ? exceldata.ccc : "";
        ws.Cell(i, 4).Value = exceldata.ddd != null && !string.IsNullOrEmpty(exceldata.ddd) ? exceldata.ddd : "";
        i++;
   }
 
    wb.Properties.Author = "Sam";
    wb.Properties.Title = "Title";
    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=MySample.xlsx");

    using (MemoryStream memoryStream = new MemoryStream())
    {
        wb.SaveAs(memoryStream);
        memoryStream.WriteTo(Response.OutputStream);
        _bytes = memoryStream.ToArray();
        string filepath = ConfigurationManager.AppSettings["FolderPath"].ToString();
        if (!Directory.Exists(filepath))
        {
           Directory.CreateDirectory(filepath);
        }
        filepath = Path.Combine(filepath, "MySample.xls");
        memoryStream.Close();
    }

    if (_IsResponse)
    {
        Response.Flush();
    }

    return _bytes;









No comments:

Post a Comment