csharp:using OpenXml SDK 2.0 and ClosedXML read excel file

https://open

引用:

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using DocumentFormat.Open
　　
 string filename = "20160816000.xlsx";//220160815 //Book1 涂聚文测试注:这两个文件的工作内容一样 2016081600011.xlsx 此文件21KB 20160816000.xlsx 此文件容量19.1M 不规则并有隐藏列,出现读取列问题,只能用System.Data.OleDb.OleDbConnection    string sheename = "";    /// <summary>    ///     /// </summary>    public Open
　　
 /// <summary>    /// 涂聚文//hide column C    /// s.SetColumnHidden(2,true); https://msdn.microsoft.com/en-us/library/office/ff956189(v=office.14).aspx     /// //hide IRow 2    ///  r2.ZeroHeight = true;    /// 否隐藏（isColumnHidden）    /// 20150820    /// 七夕节 涂聚文注: 隐瞒列读不出来    /// </summary>    /// <param name="fileName">文件名</param>    /// <param name="sheetName">工作表名</param>    /// <returns></returns>    public static DataTable ReadIdDataTable(string fileName, string sheetName)    {      DataTable dataTable = new DataTable();      try      {        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))        {          WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;          // spreadSheetDocument.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;          // spreadSheetDocument.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;          //spreadSheetDocument.WorkbookPart.Workbook.Sheets;          Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();          //IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();//第一个工作表          //string relationshipId = theSheet.FirstOrDefault().ExtendedAttributes.ElementAt(0); //工作表          // numID = sheets.Count();          WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(theSheet.Id);//第一个工作表           Worksheet workSheet = worksheetPart.Worksheet;          SheetData sheetData = workSheet.GetFirstChild<SheetData>();          //IEnumerable<Row> rows = sheetData.Descendants<Row>();          IEnumerable<Row> rows = sheetData.Descendants<Row>(); //          //          // var list = sheetData.Descendants<Row>().Where((r) => r.Hidden != null && r.Hidden.Value).Select(r => r.RowIndex.Value).ToList<uint>();          //          //foreach (Cell cell in rows.ElementAt(0))          //{          //  dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell)); //标题          //}          for (int j = 0; j < 59; j++)          {            dataTable.Columns.Add(j.ToString(), typeof(string));          }          foreach (Row row in rows)          {            DataRow dataRow = dataTable.NewRow();            //隐藏的列未显示            //for (int i = 0; i < row.Descendants<Cell>().Count(); i++)            //{            //  dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));            //}            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)            {                           //if (row.Hidden == true)              //{              //  dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));              //}              //else              //{              dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));                            //}            }            //row.getZeroHeight()            dataTable.Rows.Add(dataRow);          }        }        dataTable.Rows.RemoveAt(0);      }      catch (Exception ex)      {        ex.Message.ToString();      }      return dataTable;    }    /// <summary>    ///     /// </summary>    /// <param name="FileName"></param>    /// <param name="columnNumber"></param>    public void HideColumn(string FileName, UInt32Value columnNumber)    {      using (SpreadsheetDocument document = SpreadsheetDocument.Open(        FileName, true))      {        IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1");        if (sheets.Count() == 0)        {          // The specified worksheet does not exist.          return;        }        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);        Worksheet worksheet = worksheetPart.Worksheet;        Columns columns1 = GenerateColumns(columnNumber);        // The column element is behind the SheetFormatProperties element.        worksheet.InsertAfter(columns1, worksheet.SheetFormatProperties);        worksheet.Save();      }    }    // Creates an Columns instance and adds its children.    public Columns GenerateColumns(UInt32Value ColumnIndex)    {      Columns columns1 = new Columns();      Column column1 = new Column() { Min = ColumnIndex, Max = ColumnIndex, Width = 0D, Hidden = true, CustomWidth = true };      columns1.Append(column1);      return columns1;    }      /// <summary>      /// Reads the specified file save path.      /// </summary>      /// <param name="fileSavePath">The file save path.</param>      /// <returns></returns>      public DataSet Read(string fileSavePath)      {          DataSet resultSet = new DataSet();          using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileSavePath, false))          {          WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;          IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();          foreach (Sheet sheet in sheets)          {          DataTable dt = new DataTable();          string relationshipId = sheet.Id.Value;          string sheetName = sheet.SheetId;          dt.TableName = sheet.SheetId;          WorksheetPart worksheetPart =          (WorksheetPart) spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);          Worksheet workSheet = worksheetPart.Worksheet;          SheetData sheetData = workSheet.GetFirstChild<SheetData>();          IEnumerable<Row> rows = sheetData.Descendants<Row>();          foreach (Cell cell in rows.ElementAt(0))          {          dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));          }          List<Row> rowsList = new List<Row>();          rowsList = rows.ToList();          //Start from 1, first row is header.          for ( int iterRowIndex = 1 ; iterRowIndex < rowsList.Count ; iterRowIndex ++) //this will also include your header row…          {          Row row = rowsList[iterRowIndex];          DataRow tempRow = dt.NewRow();          for (int i = 0; i < row.Descendants<Cell>().Count(); i++)          {          tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));          }          dt.Rows.Add(tempRow);          }          resultSet.Tables.Add(dt);          }          }          return resultSet;      }    /// <summary>    ///    /// </summary>    /// <param name="document"></param>    /// <param name="cell"></param>    /// <returns></returns>    private static string GetCellValue(SpreadsheetDocument document, Cell cell)    {      //try      //{        SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;        string value = cell.CellValue.Inner
　　
  /// <summary>  ///读取工作表名  /// </summary>  public class SheetNameInfo  {    private int _sheetId;    private string _sheetName;    private string _rid;    /// <summary>    ///    /// </summary>    public int SheetID    {      get { return _sheetId; }      set { _sheetId = value; }    }    /// <summary>    ///    /// </summary>    public string SheetName    {      get { return _sheetName; }      set { _sheetName = value; }    }    /// <summary>    ///    /// </summary>    public string Rid    {      get { return _rid; }      set { _rid = value; }    }  }

　　



原标题：csharp:using OpenXml SDK 2.0 and ClosedXML read excel file
关键词：xml