如何从Excel工作表中检索列名? [英] How to retrieve column names from a excel sheet?

问题描述

使用EPPlus我正在将数据写入多张纸.如果未创建工作表，那么我将添加工作表，否则将检索已使用的行，并从该行中添加数据并保存

Using EPPlus I'm writing data to multiple sheets. If a sheet is not created I'm adding a sheet else I'm retrieving the used rows and adding data from that row and saving it

 FileInfo newFile = new FileInfo("Excel.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
  var ws = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == language.Culture);
  if (ws == null)
  {
     worksheet = xlPackage.Workbook.Worksheets.Add(language.Culture);
     //writing data
  }
  else
  {
    worksheet = xlPackage.Workbook.Worksheets[language.Culture];
    colCount = worksheet.Dimension.End.Column;
    rowCount = worksheet.Dimension.End.Row;
    //write data
   }
   worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
   xlPackage.Save();

效果很好.

现在我想使用LinqToExcel检索excel中每个工作表的列名，这是我的代码

Now I want to retrieve the column names of each sheet in the excel using LinqToExcel and this is my code

  string sheetName = language.Culture;
  var excelFile = new ExcelQueryFactory(excelPath);
  IQueryable<Row> excelSheetValues = from workingSheet in            excelFile.Worksheet(sheetName) select workingSheet;
  string[] headerRow = excelFile.GetColumnNames(sheetName).ToArray();

在标题行，它抛出了异常

At header row it is throwing me an exception

An OleDbException exception was caught
External table is not in the expected format.

但是我不想使用Oledb，也不想与Linq To Excel一起使用.

But I don't want to use Oledb and want to work with Linq To Excel.

注意:当我使用单张纸而不是多张纸时 它工作正常并检索所有列.我要去哪里错了.

Note: When I'm working with single sheet rather than multiple sheets it is working fine and retrieving all columns. Where am I going wrong.

推荐答案

(基于OP的评论)

AutoFitColumn功能始终有些棘手.要记住的重要事情是在加载单元数据之后调用它.

The AutoFitColumn function has always been a little touchy. The important thing to remember is to call it AFTER you load the cell data.

但是，如果您要使用最小宽度(当列很窄并且要使用最小宽度时)，我会发现EPP不可靠.即使将minimumWidth传递给函数重载之一，它似乎总是使用工作表的DefualtColWidth.

But if you want a use a minimum width (when columns are very narrow and you want to use a minimum) I find EPP to be unreliable. It seems to always use DefualtColWidth of the worksheet even if you pass in a minimumWidth to one of the function overloads.

这是我的解决方法:

[TestMethod]
public void Autofit_Column_Range_Test()
{
    //http://stackoverflow.com/questions/31165959/how-to-retrieve-column-names-from-a-excel-sheet

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.Add(new DataColumn("Nar", typeof(int))); //This would not be autofitted without the workaround since the default width of a new ws, usually 8.43
    datatable.Columns.Add(new DataColumn("Wide Column", typeof(int)));
    datatable.Columns.Add(new DataColumn("Really Wide Column", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i * 100;
        datatable.Rows.Add(row);
    }

    var existingFile2 = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile2.Exists)
        existingFile2.Delete();

    using (var package = new ExcelPackage(existingFile2))
    {
        //Add the data
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells.LoadFromDataTable(datatable, true);

        //Keep track of the original default of 8.43 (excel default unless the user has changed it in their local Excel install)
        var orginaldefault = ws.DefaultColWidth;
        ws.DefaultColWidth = 15;

        //Even if you pass in a miniumWidth as the first parameter like '.AutoFitColumns(15)' EPPlus usually ignores it and goes with DefaultColWidth
        ws.Cells[ws.Dimension.Address].AutoFitColumns();

        //Set it back to what it was so it respects the user's local setting
        ws.DefaultColWidth = orginaldefault;

        package.Save();
    }
}

这篇关于如何从Excel工作表中检索列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！