• 使用OpenXml把Excel中的数据导出到DataSet中


    public class OpenXmlHelper
    {
        /// <summary>
        /// 读取Excel数据到DataSet中,默认读取所有Sheet中的数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="sheetNames">Sheet名称列表,默认为null查询所有Sheet中的数据</param>
        /// <returns></returns>
        public static DataSet ReadExcel(string filePath, params string[] sheetNames)
        {
            try
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, true))
                {
                    IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.Descendants<Sheet>();
                    if (sheetNames != null && sheetNames.Length > 0)
                    {
                        sheets = sheets.Where(s => sheetNames.ToList().Contains(s.Name));
                    }
                    DataSet ds = new DataSet();
                    SharedStringTable stringTable = doc.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    foreach (Sheet sheet in sheets)
                    {
                        WorksheetPart sheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);
                        IEnumerable<Row> rows = sheetPart.Worksheet.Descendants<Row>();
                        DataTable dt = new DataTable(sheet.Name);
                        foreach (Row row in rows)
                        {
                            if (row.RowIndex == 1)
                            {
                                GetDataColumn(row, stringTable, dt);
                            }
                            GetDataRow(row, stringTable, dt);
                        }
                        ds.Tables.Add(dt);
                    }
                    return ds;
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
                return null;
            }
        }
    
      #region help methods
    /// <summary> /// 把Spreadsheet行中的数据导出到DataTable中。 /// </summary> /// <param name="row">Spreadsheet行</param> /// <param name="stringTable">共享字符串表</param> /// <param name="dt">DataTable</param> private static void GetDataRow(Row row, SharedStringTable stringTable, DataTable dt) { DataRow dr = dt.NewRow(); string cellValue = string.Empty; int i = 0; int nullCellCount = i; foreach (Cell cell in row) { cellValue = GetCellValue(cell, stringTable); if (cellValue == string.Empty) nullCellCount++; dr[i] = cellValue; i++; } //如果一整行数据都没有数据,则不添加此行到DataTable中 if (nullCellCount != i) dt.Rows.Add(dr); } /// <summary> /// 从Spreadsheet行中读取表头信息 /// </summary> /// <param name="row">Spreadsheet行</param> /// <param name="stringTable">共享字符串表</param> /// <param name="dt">DataTable</param> private static void GetDataColumn(Row row, SharedStringTable stringTable, DataTable dt) { DataColumn col = new DataColumn(); Dictionary<string, int> columnCountDict = new Dictionary<string, int>(); foreach (Cell cell in row) { string cellValue = GetCellValue(cell, stringTable); col = new DataColumn(cellValue); //由于Excel中的数据表列标题可以重复,而DataTable中不允许重复,因此在重复的列标题后追加递增数字 if (dt != null && dt.Columns.Contains(cellValue)) { if (!columnCountDict.ContainsKey(cellValue)) { columnCountDict.Add(cellValue, 0); } col.ColumnName = cellValue + (columnCountDict[cellValue]++); } dt.Columns.Add(col); } } /// <summary> /// 获取Spreadsheet单元格的值 /// </summary> /// <param name="cell">Spreadsheet单元格</param> /// <param name="stringTable">共享字符串表</param> /// <returns>Spreadsheet单元格的值</returns> private static string GetCellValue(Cell cell, SharedStringTable stringTable) { string value = string.Empty; try { if (cell.ChildElements.Count == 0) { return value; } value = cell.CellValue.InnerText; if (cell.DataType != null && cell.DataType == CellValues.SharedString) { value = stringTable.ChildElements[int.Parse(value)].InnerText; } } catch (Exception) { value = "N/A"; } return value; }
      #endregion }
  • 相关阅读:
    .NET重构(七):VS报表的制作
    【Linq】标准查询操作符
    1 TaskQueue 实现Task 队列
    1 疑惑处理
    1 JSONP
    1 Web 知识基础
    20 闭包
    1 基础知识
    Web 常用
    【Winform】2 Button
  • 原文地址:https://www.cnblogs.com/jiao1855/p/5751560.html
Copyright © 2020-2023  润新知