Question

我对诉诸StackOverflow以达到基本目的(似乎如此)表示仇恨,但我过去几小时一直在与微软交战,似乎正在打着死端。我正试图读(大)Excel 2007+电子表格,谷歌已经向我通报说,使用开放Xml SDK是一种很受欢迎的选择。因此,我打了枪,读了一些辅导,检查了微软自己的图书馆网页,而且根本没有。

我使用的是小幅测试电子表格,只有一栏数字和缩略语之一,随后将进行大规模测试。我曾尝试过几个与我即将到任的节目类似的节目,其中没有一个读过数据。下面的法典主要取自另一个似乎行之有效的StackOverflow法,对我来说不是这样。我看上去的是,你用这个版本进行gu检查/点击/帮助,因为其伤势可能低于我今天写过的话。

static void ReadExcelFileSAX(string fileName)
    {
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, true))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

            OpenXmlPartReader reader = new OpenXmlPartReader(worksheetPart);
            string text;
            string rowNum;
            while (reader.Read())
            {
                if (reader.ElementType == typeof(Row))
                {
                    do
                    {
                        if (reader.HasAttributes)
                        {
                            rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
                            Console.Write("rowNum: " + rowNum); //we never even get here, I tested it with a breakpoint
                        }

                    } while (reader.ReadNextSibling()); // Skip to the next row
                    Console.ReadKey();
                    break; // We just looped through all the rows so no need to continue reading the worksheet
                }
                if (reader.ElementType == typeof(Cell))
                {

                }

                if (reader.ElementType != typeof(Worksheet)) // Dont  want to skip the contents of the worksheet
                    reader.Skip(); // Skip contents of any node before finding the first row.
            }
            reader.Close();
            Console.WriteLine();
            Console.ReadKey();
        }
    }

而另一方面,在使用开放Xml SDK(I)方面,是否有任何很好的替代办法?

Answer 1

我认为,你在阅读浏览时采用了错误的<代码>Worksheet Part。

段次

workbookPart.WorksheetParts.First();

gets the first WorksheetPart of the collection which must not necessarily be the first worksheet as you see it in Microsoft Excel.

So, iterate through all WorksheetParts and you should see some output on your console window.

static void ReadExcelFileSAX(string fileName)
{
  using (SpreadsheetDocument spreadsheetDocument = 
                                   SpreadsheetDocument.Open(fileName, true))
  {
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

    // Iterate through all WorksheetParts
    foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
    {          
      OpenXmlPartReader reader = new OpenXmlPartReader(worksheetPart);
      string text;
      string rowNum;
      while (reader.Read())
      {
        if (reader.ElementType == typeof(Row))
        {
          do
          {
            if (reader.HasAttributes)
            {
              rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
              Console.Write("rowNum: " + rowNum);
            }

          } while (reader.ReadNextSibling()); // Skip to the next row

          break; // We just looped through all the rows so no 
                 // need to continue reading the worksheet
        }

        if (reader.ElementType != typeof(Worksheet))
          reader.Skip(); 
      }
      reader.Close();      
    }
  }  
}

阅读所有细胞价值时,使用以下功能(所有错误处理细节都省略):

static void ReadAllCellValues(string fileName)
{
  using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
  {
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

    foreach(WorksheetPart worksheetPart in workbookPart.WorksheetParts)
    {
      OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

      while (reader.Read())
      {
        if (reader.ElementType == typeof(Row))
        {
          reader.ReadFirstChild();

          do
          {
            if (reader.ElementType == typeof(Cell))
            {
              Cell c = (Cell)reader.LoadCurrentElement();

              string cellValue;

              if (c.DataType != null && c.DataType == CellValues.SharedString)
              {
                SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));

                cellValue = ssi.Text.Text;
              }
              else
              {
                cellValue = c.CellValue.InnerText;
              }

              Console.Out.Write("{0}: {1} ", c.CellReference, cellValue);
            }
          } while (reader.ReadNextSibling());
          Console.Out.WriteLine();
        }            
      }
    }   
  }
}

In the code above you see that cells with data type SharedString must be handled using the SharedStringTablePart.

Answer 2

在阅读空白囚室时,我使用的是投影机外分配的变量,在路边,我正在检查,如果一栏指数比我的变量高,因为每组读后正在加热。如果这不符合要求,我将用我想要的价值填满一栏。这是我用来把空白囚室赶上我尊重的栏目。该守则是:

public static DataTable ReadIntoDatatableFromExcel(string newFilePath)
        {
            /*Creating a table with 20 columns*/
            var dt = CreateProviderRvenueSharingTable();

            try
            {
                /*using stream so that if excel file is in another process then it can read without error*/
                using (Stream stream = new FileStream(newFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
                    {
                        var workbookPart = spreadsheetDocument.WorkbookPart;
                        var workbook = workbookPart.Workbook;

                        /*get only unhide tabs*/
                        var sheets = workbook.Descendants<Sheet>().Where(e => e.State == null);

                        foreach (var sheet in sheets)
                        {
                            var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

                            /*Remove empty sheets*/
                            List<Row> rows = worksheetPart.Worksheet.Elements<SheetData>().First().Elements<Row>()
                                .Where(r => r.InnerText != string.Empty).ToList();

                            if (rows.Count > 1)
                            {
                                OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

                                int i = 0;
                                int BTR = 0;/*Break the reader while empty rows are found*/

                                while (reader.Read())
                                {
                                    if (reader.ElementType == typeof(Row))
                                    {
                                        /*ignoring first row with headers and check if data is there after header*/
                                        if (i < 2)
                                        {
                                            i++;
                                            continue;
                                        }

                                        reader.ReadFirstChild();

                                        DataRow row = dt.NewRow();

                                        int CN = 0;

                                        if (reader.ElementType == typeof(Cell))
                                        {
                                            do
                                            {
                                                Cell c = (Cell)reader.LoadCurrentElement();

                                                /*reader skipping blank cells so data is getting worng in datatable s rows according to header*/
                                                if (CN != 0)
                                                {
                                                    int cellColumnIndex =
                                                        ExcelHelper.GetColumnIndexFromName(
                                                            ExcelHelper.GetColumnName(c.CellReference));

                                                    if (cellColumnIndex < 20 && CN < cellColumnIndex - 1)
                                                    {
                                                        do
                                                        {
                                                            row[CN] = string.Empty;
                                                            CN++;
                                                        } while (CN < cellColumnIndex - 1);
                                                    }
                                                }

                                                /*stopping execution if first cell does not have any value which means empty row*/
                                                if (CN == 0 && c.DataType == null && c.CellValue == null)
                                                {
                                                    BTR++;
                                                    break;
                                                }

                                                string cellValue = GetCellValue(c, workbookPart);
                                                row[CN] = cellValue;
                                                CN++;

                                                /*if any text exists after T column (index 20) then skip the reader*/
                                                if (CN == 20)
                                                {
                                                    break;
                                                }
                                            } while (reader.ReadNextSibling());
                                        }

                                        /*reader skipping blank cells so fill the array upto 19 index*/
                                        while (CN != 0 && CN < 20)
                                        {
                                            row[CN] = string.Empty;
                                            CN++;
                                        }

                                        if (CN == 20)
                                        {
                                            dt.Rows.Add(row);
                                        }
                                    }
                                    /*escaping empty rows below data filled rows after checking 5 times */
                                    if (BTR > 5)
                                        break;
                                }
                                reader.Close();
                            }                            
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

  private static string GetCellValue(Cell c, WorkbookPart workbookPart)
        {
            string cellValue = string.Empty;
            if (c.DataType != null && c.DataType == CellValues.SharedString)
            {
                SharedStringItem ssi =
                    workbookPart.SharedStringTablePart.SharedStringTable
                        .Elements<SharedStringItem>()
                        .ElementAt(int.Parse(c.CellValue.InnerText));
                if (ssi.Text != null)
                {
                    cellValue = ssi.Text.Text;
                }
            }
            else
            {
                if (c.CellValue != null)
                {
                    cellValue = c.CellValue.InnerText;
                }
            }
            return cellValue;
        }

public static int GetColumnIndexFromName(string columnNameOrCellReference)
        {
            int columnIndex = 0;
            int factor = 1;
            for (int pos = columnNameOrCellReference.Length - 1; pos >= 0; pos--)   // R to L
            {
                if (Char.IsLetter(columnNameOrCellReference[pos]))  // for letters (columnName)
                {
                    columnIndex += factor * ((columnNameOrCellReference[pos] -  A ) + 1);
                    factor *= 26;
                }
            }
            return columnIndex;
        }

        public static string GetColumnName(string cellReference)
        {
            /* Advance from L to R until a number, then return 0 through previous position*/
            for (int lastCharPos = 0; lastCharPos <= 3; lastCharPos++)
                if (Char.IsNumber(cellReference[lastCharPos]))
                    return cellReference.Substring(0, lastCharPos);

            throw new ArgumentOutOfRangeException("cellReference");
        }

Code works for: 1. This code reads blank cells 2. skip empty rows after reading complete. 3. read the sheet from first in ascending order 4. if excel file is being used by another process, OpenXML still reads that.

友情链接