EPPlus将Excel Spreadsheet列转换为随机负值 [英] EPPlus converts Excel Spreadsheet column to random negative value

问题描述

我有一个C＃方法，请参见下面的代码，该方法使用EPPlus生成Excel电子表格。该方法接受3个字符串，其中一个是管道分隔的字符串，名为csv，其中包含我的数据。我正在使用的数据有14列，而在第14列中，我的数据看起来像是：103.01.06或01.01，或其他一些用句点分隔的数值。我看到的问题是，如果值为103.01.06，则EPPlus会将其转换为值-656334。有什么方法可以解决此问题？

I have a C# method, see code below, that generates an Excel spreadsheet using EPPlus. The method takes in 3 strings, one of which is a pipe delimited string named csv that has my data in it. The data I'm using has 14 columns, and in the 14th column I have data that looks like: 103.01.06, or 01.01, or some other numeric values separated by periods. The problem I'm seeing is that if the value is 103.01.06, then EPPlus transforms that into the value -656334. Is there some way to fix this?

    private void GenerateSpreadsheet(string id, string csv, string worksheetName)
    {
        using (ExcelPackage pck = new ExcelPackage())
        {
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add(worksheetName);

            ws.Cells["A1"].LoadFromText(csv, new ExcelTextFormat{Delimiter = '|'}); 

            Response.AddHeader("Content-Disposition",
                String.Format("attachment; filename={0}.xlsx", id));
            Response.ContentType =
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            Response.BinaryWrite(pck.GetAsByteArray());
            Response.End();
        }
    }

推荐答案

如果您没有通过 ExcelTextFormat 对象的Datatypes属性指定列类型，则EPPlus假定该列为未知。由于您有14列，并且在第14列中遇到数据问题，因此需要为每个前面的列指定类型。在不知道您的数据类型的情况下，我只列出了14个 eDataTypes.String 的实例，但是 eDataTypes.Number 等也将列出。

If you don't specify a columns type via the Datatypes property of the ExcelTextFormat object then EPPlus assumes unknown for the column. Since you have fourteen columns and you are having trouble with data in the fourteenth column you'll need to specify types for each prior column. Without knowing your Datatypes I've just listed fourteen instances of eDataTypes.String, but eDataTypes.Number etc. would also work.

EPPlus在为列分配类型时会做出猜测。

EPPlus makes a guess when assigning a type to a column.

private void GenerateSpreadsheet(string id, string csv, string worksheetName)
{
    using (ExcelPackage pck = new ExcelPackage())
    {
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add(worksheetName);

        ws.Cells["A1"].LoadFromText(csv, new ExcelTextFormat { Delimiter = '|', 
            DataTypes = new eDataTypes[] { eDataTypes.String, eDataTypes.String, 
                eDataTypes.String, eDataTypes.String, eDataTypes.String,
                eDataTypes.String, eDataTypes.String, eDataTypes.String,
                eDataTypes.String, eDataTypes.String, eDataTypes.String, 
                eDataTypes.String, eDataTypes.String, eDataTypes.String } }); 

        Response.AddHeader("Content-Disposition",
            String.Format("attachment; filename={0}.xlsx", id));
        Response.ContentType =
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.BinaryWrite(pck.GetAsByteArray());
        Response.End();
    }
}

这篇关于EPPlus将Excel Spreadsheet列转换为随机负值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！