/// <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);
}
