c# Excelpackage将单元格范围读入数据表

本文关键字：数据表 范围 单元格 Excelpackage | 更新日期: 2023-09-27 18:14:20

使用c#，我可以使用Excel互操作在单元格范围内读取。但它很难与美元价值和百分比保持一致。所以我在试用EEPLus。

FileInfo newFile = new FileInfo(@excelFilePath);
ExcelPackage pck = new ExcelPackage(newFile);
var Summary = workbook1.Worksheets[1];

，然后使用下面的

Convert.ToString(Summary.Cells["I35"].Value);

我得到了一个不错的值。

但是我想做的是下面这些

 Summary.Cells["E29:P32"].Value

并将其放入数据表中。有一个ImportDataTable的方法，但它从datatable移动到excel。是否存在类似的简单的excel数据表?

欢呼。

c# Excelpackage将单元格范围读入数据表

没有内置方法，但您可以尝试这样做:

var range = summary.Cells["E6:G7"];
DataTable tbl = GetDataTableFromRange(range);

和GetDataTableFromRange方法:

public static DataTable GetDataTableFromRange(ExcelRange range)
{
  DataTable tbl = new DataTable();
  tbl.Columns.Add("Column1");
  tbl.Columns.Add("Column2");
  tbl.Columns.Add("Column3");
  int dataTableColumn = 0;
  DataRow newRow = null;
  int currRow = -1;
  foreach (var item in range)
  {
    if (currRow != item.Start.Row)
    {
      newRow = tbl.NewRow();
      tbl.Rows.Add(newRow);
      dataTableColumn = 0;
      currRow = item.Start.Row;
    }
    newRow[dataTableColumn] = item.Value.ToString();
    dataTableColumn++;
  }
  return tbl;
}

你可以这样做:

public bool readXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        string queryString = "INSERT INTO tableName VALUES";        //Here I am using "blind insert". You can specify the column names Blient inset is strongly not recommanded
        string eachVal = "";
        bool status;
        for (int row = 1; row <= rowCount; row++)
        {
            queryString += "(";
            for (int col = 1; col <= colCount; col++)
            {
                eachVal = worksheet.Cells[row, col].Value.ToString().Trim();
                queryString += "'" + eachVal + "',";
            }
            queryString = queryString.Remove(queryString.Length - 1, 1);    //removing last comma (,) from the string
            if (row % 1000 == 0)        //On every 1000 query will execute, as maximum of 1000 will be executed at a time. 
            {
                queryString += ")";
                status = this.runQuery(queryString);    //executing query
                if (status == false)
                    return status;
                queryString = "INSERT INTO tableName VALUES";
            }
            else
            {
                queryString += "),";
            }
        }
        queryString = queryString.Remove(queryString.Length - 1, 1);    //removing last comma (,) from the string
        status = this.runQuery(queryString);    //executing query
        return status;
    }
}

来源:http://sforsuresh.in/read-data-excel-sheet-insert-database-table-c/

这对我来说是有效的，可以在Datatable中找到范围并存储Excel文件的值: 

    public virtual DataTable ReadExcelFileToDatable(IFormFile file) //HttpPostedFileBase file
    {
        bool hasHeader = true;
        var tbl = new DataTable();
        using (var pck = new ExcelPackage(file.OpenReadStream())) // file.InputStream 
        {
            var ws = pck.Workbook.Worksheets.FirstOrDefault();
            if (ws != null)
            {
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    if (firstRowCell.Text != "" && firstRowCell.Text != null)
                        tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                    else
                        break;
                }
                var startRow = hasHeader ? 2 : 1;
                for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    DataRow row = tbl.Rows.Add();
                    foreach (var cell in wsRow)
                    {
                        if (cell.Text != "" && cell.Text != null)
                            row[cell.Start.Column - 1] = cell.Text;
                    }
                }
                return tbl; 
            }
            else
            {
                return tbl;
            }
        }
    }
相关文章：