CODEWITHSUNDEEP
asp.netexcelvbaexport-to-excelepplus
user1685989
user1685989

Reputation: 33

Using EPPlus -export to excel, set range as Text to a specific column

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.

Upvotes: 1

Views: 7256

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460158

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?

Upvotes: 2

Ernie S
Ernie S

Reputation: 14250

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

Upvotes: 1

Related Questions