/// <summary> /// Excel文件转换为DataTable. /// </summary> public static BatchUploadResponse ExcelToDataTable(string templateFileName, string fileName, DataTable dt) { BatchUploadResponse response = new BatchUploadResponse(fileName); if (!File.Exists(fileName)) { response.AddError(0, "文件不存在!"); return(response); } Workbook workbook = new Workbook(); // 打开模板 workbook.Open(templateFileName); Worksheet worksheet = workbook.Worksheets[dt.TableName]; if (worksheet == null) { response.AddError(0, "模板和配置文件不匹配,请联系管理员。"); return(response); } int startRow = worksheet.Cells.MaxDataRow; if (startRow > 0) { startRow += 1; } // 打开文件 workbook.Open(fileName); worksheet = workbook.Worksheets[dt.TableName]; if (worksheet == null) { response.AddError(0, "上传的文件和模板不匹配,请下载最新模板。"); return(response); } int maxRow = worksheet.Cells.MaxDataRow; int maxCol = worksheet.Cells.MaxDataColumn; //datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1); for (int ii = startRow; ii <= maxRow; ii++) { DataRow dr = dt.NewRow(); try { for (int jj = 0; jj <= dt.Columns.Count - 1; jj++) { DataColumn col = dt.Columns[jj]; object value = worksheet.Cells[ii, jj].Value; if (!string.IsNullOrEmpty(col.Namespace)) { value = GetMappingValue(col.Namespace, value); } dr[jj] = Utils.DataConvert(col.DataType, value); } dt.Rows.Add(dr); } catch (Exception ex) { response.AddError(ii, ex.Message); } } response.Data = dt; return(response); }