通过 OpenXML 将 DataTable 导入 Excel

Question

我有一个像这样的 DataTable

Col1    Col2    Col3        col4
GRPs    2009    69952.4     a
GRPs    2010    58949.8     a
GRPs    2009    37251.2     b
GRPs    2010    35433.9     b
GRPs    2009    28039.2     c
GRPs    2010    35079.4     c
SOC     2009    69952.4     a
SOC     2010    58949.8     a
SOC     2009    37251.2     b
SOC     2010    35433.9     b
SOC     2009    28039.2     c
SOC     2010    35079.4     c

我需要使用 OpenXML 将此格式“转换”为 excel 文件

   A      B         C         D      E       F        G      H        I
1         2009      2010             2009    2010            2009     2010
2  GRPs   69952.4   58949.8          37251.2 35433.9         28039.2  35079.4
3  SOC    69952.4   58949.8          37251.2 35433.9         28039.2  35079.4

提前致谢

Answer 1

它有点像这样（抱歉这不是完整的代码，但它应该有帮助）：

//Column headers
static string[] headerColumns = new string[] { "A", "B", "C" };

//Used for header column counting
static string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

         foreach (char c in alphabet.Split(alphabet[dt.Columns.Count])[0])
                headerColumns[alphabet.IndexOf(c)] = c.ToString();

            //Loops through the data table and appends the data rows onto sheet data
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //Builds a list of values from the data row
                List<string> values = new List<string>();
                for (int i = 0; i < dt.Columns.Count; i++)
                    values.Add(dt.Rows[r][i].ToString());

                //Creates a spreadsheet row from the list of values
                Spreadsheet.Row contentRow = CreateContentRow(values, r + 2);

                //Appends the row to the sheetData
                sheetData.AppendChild(contentRow);
            }

    private static Spreadsheet.Row CreateContentRow(List<string> values, int index)
    {
        //Create the new row.
        Spreadsheet.Row r = new Spreadsheet.Row();
        r.RowIndex = (UInt32)index;

        //Create the cells that contain the data.
        for (int i = 0; i < headerColumns.Length; i++)
        {
            Spreadsheet.Cell c = new Spreadsheet.Cell();
            c.CellReference = headerColumns[i] + index;
            Spreadsheet.CellValue v = new Spreadsheet.CellValue();
            v.Text = values[i];
            c.AppendChild(v);
            r.AppendChild(c);
        }
        return r;
    }