1

am using EPPlus to export data to excel(from MemoryStream), below is the code 

private static MemoryStream ExportToExcelAsStram(DataSet ds)
{
    MemoryStream ms = new MemoryStream();
    ExcelPackage package = new ExcelPackage(ms);
    try
    {
        for (int i = 0; i < ds.Tables.Count; i++)
        {

            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add((ds.Tables[i].Rows[i]["Date"]).ToString());

            using (ExcelRange rng = worksheet.Cells["B1 : B" + (ds.Tables[i].Rows.Count + 1)])
            {
                rng.Style.Numberformat.Format = "#";
            }

            //worksheet.Cells["B1 : B" + (ds.Tables[i].Rows.Count + 1)].Style.Numberformat.Format = "@";
            worksheet.Cells["A1"].LoadFromDataTable(ds.Tables[i], true);

            //Format the header for column 1-9
            using (ExcelRange range = worksheet.Cells[1, 1, 1, 12])
            {
                range.Style.Font.Bold = true;
                range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkRed);
                range.Style.Font.Color.SetColor(System.Drawing.Color.White);
            }

            worksheet.Cells["A1:L" + (ds.Tables[i].Rows.Count + 1)].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, System.Drawing.Color.Black);
            worksheet.Cells["A1:L" + (ds.Tables[i].Rows.Count + 1)].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:L" + (ds.Tables[i].Rows.Count + 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        }

    }
    catch (Exception ex)
    {
        throw ex;
    }
    package.Save();
    ms.Position = 0;
    return ms;
}

i need to set the format of column B as text. i gave the range specifically for column B, but once the excel is generated this formatting is applied for all other columns. Please help me solve this... thanks in advance.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
user1685989
  • 33
  • 1
  • 5

2 Answers2

2

Apply the style to the range after you have loaded it:

worksheet.Cells["A1"].LoadFromDataTable(ds.Tables[i], true);
ExcelRange rng = worksheet.Cells["B1:B" + (ds.Tables[i].Rows.Count + 1)];
rng.Style.Numberformat.Format = "#";

Btw: Using for excelrange is needless: EPPlus - Do I need to call Dispose on objects like ExcelRange?

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
1

Remove the spaces from the string address you are building "B1 : B":

using (ExcelRange rng = worksheet.Cells["B1:B" + (dt.Rows.Count + 1)])
{
    rng.Style.Numberformat.Format = "0.00";
}

That will likely trip up excel.

[TestMethod]
public void ExportToExcelAsStram()
{
    //http://stackoverflow.com/questions/28714488/using-epplus-export-to-excel-set-range-as-text-to-a-specific-column
    //Throw in some data
    var dt = new DataTable("tblData");
    dt.Columns.Add(new DataColumn("Date", typeof(DateTime)));
    dt.Columns.Add(new DataColumn("Col2", typeof(int)));
    dt.Columns.Add(new DataColumn("Col3", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = dt.NewRow();
        row["Date"] = DateTime.Now.AddDays(i);
        row["Col2"] = i * 10;
        row["Col3"] = i * 100;
        dt.Rows.Add(row);
    }

    var ds = new DataSet();
    ds.Tables.Add(dt);

    MemoryStream ms = new MemoryStream();
    ExcelPackage package = new ExcelPackage(ms);
    try
    {
        for (int i = 0; i < ds.Tables.Count; i++)
        {

            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add((ds.Tables[i].Rows[i]["Date"]).ToString());

            using (ExcelRange rng = worksheet.Cells["B1:B" + (ds.Tables[i].Rows.Count + 1)])
            {
                rng.Style.Numberformat.Format = "0.00";
            }

            //worksheet.Cells["B1 : B" + (ds.Tables[i].Rows.Count + 1)].Style.Numberformat.Format = "@";
            worksheet.Cells["A1"].LoadFromDataTable(ds.Tables[i], true);

            //Format the header for column 1-9
            using (ExcelRange range = worksheet.Cells[1, 1, 1, 12])
            {
                range.Style.Font.Bold = true;
                range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkRed);
                range.Style.Font.Color.SetColor(System.Drawing.Color.White);
            }

            worksheet.Cells["A1:L" + (ds.Tables[i].Rows.Count + 1)].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, System.Drawing.Color.Black);
            worksheet.Cells["A1:L" + (ds.Tables[i].Rows.Count + 1)].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:L" + (ds.Tables[i].Rows.Count + 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        }

    }
    catch (Exception ex)
    {
        throw ex;
    }
    package.Save();
    ms.Position = 0;
    //return ms;

    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    ms.WriteTo(new FileStream(existingFile.FullName, FileMode.Create));
}
Ernie S
  • 13,902
  • 4
  • 52
  • 79
  • @user1685989 Humm..try the unit test above? Seemed to make the difference (note that I am using 0.00 as the format but that should not matter). – Ernie S Mar 01 '15 at 15:14
  • found some links to hide the warning in excel.. but here i applied for one column, it should not consider others.. any thoughts are appreciated! – user1685989 Mar 02 '15 at 11:26
  • 1
    @user1685989 Oh wait, if you are getting the green trangles then the problem is not with the code (althought I still think you need to remove those spaces). The problem is the data table itself. You are probably storing numbers as strings or vice versa. Post that part of your code or check out this post: http://stackoverflow.com/questions/28591763/epplus-how-to-know-the-format-of-the-worksheet-cell/28630723#28630723 – Ernie S Mar 02 '15 at 13:09
  • Thank you.. i didnt think that way.. will try, currently in the datatable everything is string.... – user1685989 Mar 03 '15 at 05:13