将此excel转换为数据表

本文关键字：数据表 转换 excel 将此 | 更新日期: 2023-09-27 18:21:48

我以前成功地将Excel转换为数据表，但现在，我有一个奇怪的表格格式，下图将显示Excel表格，以及我想要实现的表格。

var workbook = Workbook.Load("file.xls");
var worksheet = workbook.Worksheets[0];
var cells = worksheet.Cells;
var dataTable = new DataTable("datatable");

for (int colIndex = cells.FirstColIndex; colIndex <= cells.LastColIndex; colIndex++)
{
    dataTable.Columns.Add(cells[0, colIndex].StringValue);
}
for (int rowIndex = cells.FirstRowIndex; rowIndex <= cells.LastRowIndex; rowIndex++)
{
    var values = new List<string>();
    foreach(var cell in cells.GetRow(rowIndex))
    {
        values.Add(cell.Value.StringValue);
    }
    dataTable.LoadDataRow(values.ToArray(), true);
}

这就是代码。当我把它和提到的.xls一起使用时，我得到的是"price:row shifted left"。示例：https://i.stack.imgur.com/2jZQY.png因此，任何关于如何解决这一问题的帮助都是非常受欢迎的。谢谢

将此excel转换为数据表

这里有一个参考链接，有人遇到了同样的问题，以及如何解决问题

如何解决Exce.Interop问题区域性设置

错误在于，.NET检查您的线程（C#或VB代码）本地化是否适合您以前安装的MS Excel本地化，如果不适合，则会"告知"Microsoft.Office.Interop库已旧或无效。您的线程本地化源自您的计算机区域设置（来自控制面板-->区域和语言）

然后有两种选择来解决这个问题：1.更改线程本地化（通过代码）2.为Office 安装语言包

第一个解决方案是这样的：

using System.Threading;     // For setting the Localization of the thread to fit
using System.Globalization; // the of the MS Excel localization, because of the MS bug.
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            excelFileName = System.IO.Path.Combine(excelPath, "Ziperty Buy Model for Web 11_11_2011.xlsm");

您可以在不打开的情况下检索它

仅对xlsm文件使用"Excel 12.0 Macro"而不是"Excel 12.0"

HDR=YES表示Excel表具有标题

 Dim myWorkBookPath AS String = "C:'test.xlsx"
    Dim mySheetName As String= "Sheet1"
    Dim myRangeAddress as String = "A1:K10"  'if you retrieve this by program, think to remove all $ signs . .Replace("$","")
         Dim myXlConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES""", myWorkBookPath)
              Dim myXlConnection = New OleDb.OleDbConnection(myXlConnectionString)
              Dim sXlCommand As OleDbCommand = New OleDbCommand(String.Format("Select * FROM [{0}${1}]", mySheetName, myRangeAddress), myXlConnection)
              Dim dt = New Data.DataTable()
              Dim da = New OleDbDataAdapter(sXlCommand)
              myXlConnection.Open()
              da.Fill(dt)
              myXlConnection.Close()
              Return dt

在.cs文件的顶部放入您的使用中

using System.Reflection; 
using Microsoft.Office.Interop.Excel;
        // TO USE:
        // 1) include COM reference to Microsoft Excel Object library
        // add namespace...
        // 2) using Excel = Microsoft.Office.Interop.Excel;
        private static void Excel_FromDataTable(<strong class="highlight">DataTable</strong> dt)
        {
            // Create an Excel object and add workbook...
            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???
            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;
                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }
            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;
            // If wanting to Save the workbook...
            workbook.SaveAs("TestExcel.xls",
                Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
            // If wanting to make Excel visible and activate the worksheet...
            excel.Visible = true;
            Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
            ((Excel._Worksheet)worksheet).Activate();
            // If wanting excel to shutdown...
            ((Excel._Application)excel).Quit();
        }
相关文章：