vba 使用 EPPlus -export to excel，将范围设置为文本到特定列

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

提问by user1685989

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

我正在使用EPPlus将数据导出到excel（来自 MemoryStream），下面是代码

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.

我需要将 B 列的格式设置为文本。我专门为 B 列提供了范围，但是一旦生成了 excel，此格式将应用于所有其他列。请帮我解决这个问题......提前致谢。

回答by Tim Schmelter

Apply the style to the range afteryou 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?

顺便说一句：用于 excelrange 是不必要的：EPPlus - 我需要在 ExcelRange 之类的对象上调用 Dispose 吗？

回答by Ernie S

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

从您正在构建的字符串地址中删除空格"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.

那可能会绊倒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));
}