//文件上传
public ActionResult UploadExcel(HttpPostedFileBase Fdata, string excelKey, string vaildURL, string saveURL)
{
LogWriter.Info(string.Format("UploadExcel - Excel导入的Key：{0} - 开始", excelKey));
if (Fdata == null || Fdata.InputStream == null)
{
return(RedirectToAction("ImportExcel", new { excelkey = excelKey, vaildURL = vaildURL, saveURL = saveURL, ErrorMsg = "数据文件没有上传，请上传要导入数据文件！" }));
}
var fileSize = Fdata.InputStream.Length;
byte[] fileBuffer = new byte[fileSize];
Fdata.InputStream.Read(fileBuffer, 0, (int)fileSize);
Fdata.InputStream.Close();
ExcelData data = null;
try
{
IImporter importer = new AsposeExcelImporter();
data = importer.Import(fileBuffer, excelKey);
}
catch (Exception ex)
{
LogWriter.Error(ex);
return(RedirectToAction("ImportExcel", new { excelkey = excelKey, vaildURL = vaildURL, saveURL = saveURL, ErrorMsg = ex.Message }));
}
var serverUrl = string.Format("{0}://{1}", Request.Url.Scheme, Request.Url.Authority);
// 验证数据
LogWriter.Info(string.Format("UploadExcel - 进入校验数据逻辑，校验地址：{0} - 开始", vaildURL));
var errors = Post <List <CellErrorInfo> >(serverUrl, vaildURL, new { data = JsonConvert.SerializeObject(data) });
LogWriter.Info(string.Format("UploadExcel - 进入校验数据逻辑，校验地址：{0} - 结束", vaildURL));
if (errors.Count > 0)
{
var errorFilePath = string.Format(@"~/ErrorExcels/{0}_有错误.xls", excelKey);
WriteErrorInfoToExcel(errors, fileBuffer, errorFilePath);
return(RedirectToAction("ImportExcel", new { excelkey = excelKey, vaildURL = vaildURL, saveURL = saveURL, ErrorCount = errors.Count, ErrorFilePath = Url.Content(errorFilePath) }));
}
// 保存数据
var dataTable = data.GetDataTable();
LogWriter.Info(string.Format("UploadExcel - 进入数据保存逻辑，保存地址：{0} - 开始", saveURL));
var strResult = Post(serverUrl, saveURL, new { data = JsonConvert.SerializeObject(dataTable, new DataTableConverter()) });
LogWriter.Info(string.Format("UploadExcel - 进入数据保存逻辑，保存地址：{0} - 结束", saveURL));
if (strResult != "Success")
{
LogWriter.Error(strResult);
return(RedirectToAction("ImportExcel", new { excelkey = excelKey, vaildURL = vaildURL, saveURL = saveURL, ErrorMsg = "数据无法保存，请联系管理员查看错误日志！" }));
}
// 清空数据，释放内存
data = null;
LogWriter.Info(string.Format("UploadExcel - Excel导入的Key：{0} - 结束", excelKey));
return(RedirectToAction("ImportExcel", new { excelkey = excelKey, vaildURL = vaildURL, saveURL = saveURL, result = dataTable.Rows.Count.ToString() }));
//return Content(strResult);
}
// Get DownloadExcelTemplate 下载Excel模板
public ActionResult DownloadExcelTemplate(string excelkey)
{
var importer = new AsposeExcelImporter();
var buffer = importer.GetExcelTemplate(excelkey);
return(File(buffer, "application/vnd.ms-excel", excelkey + ".xls"));
}
public static GlobalData CreateGlobalData(string excelFilePath)
{
try
{
#region EXCEL模板校验
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel.xlsx";
if (String.IsNullOrEmpty(templatePath))
{
throw new Exception("模板文件路径为空，无法获取全局数据");
}
if (!File.Exists(templatePath))
{
throw new Exception("没有找到指定选择路径的模板文件，无法读取数据");
}
if (String.IsNullOrEmpty(excelFilePath))
{
throw new Exception("EXCEL文件路径为空，无法获取全局数据");
}
if (!System.IO.File.Exists(excelFilePath))
{
throw new Exception("没有找到指定选择路径的EXCEL 文件，无法读取数据");
}
#endregion
var golalData = new GlobalData();
golalData.ErrorExcelPath = Path.GetDirectoryName(excelFilePath) + "\\" + Path.GetFileNameWithoutExtension(excelFilePath) + "_校验错误.xls";
var k = 1;
while (File.Exists(golalData.ErrorExcelPath))
{
golalData.ErrorExcelPath = Path.GetDirectoryName(excelFilePath) + "\\" + Path.GetFileNameWithoutExtension(excelFilePath) + "_校验错误_" + k + ".xls";
k++;
}
golalData.ImportErrorExcelPath = Path.GetDirectoryName(excelFilePath) + "\\" + Path.GetFileNameWithoutExtension(excelFilePath) + "_导入错误.xls";
k = 1;
while (File.Exists(golalData.ImportErrorExcelPath))
{
golalData.ImportErrorExcelPath = Path.GetDirectoryName(excelFilePath) + "\\" + Path.GetFileNameWithoutExtension(excelFilePath) + "_导入错误_" + k + ".xls";
k++;
}
#region 装载数据库访问对象
for (int i = 0; i < System.Configuration.ConfigurationManager.ConnectionStrings.Count; i++)
{
var conn = System.Configuration.ConfigurationManager.ConnectionStrings[i];
if (golalData.DBList.ContainsKey(conn.Name))
{
continue;
}
golalData.DBList.Add(conn.Name, SQLHelper.Create(conn));
}
#endregion
golalData.FileBuffer = null;
using (var fileStream = new FileStream(excelFilePath, FileMode.Open))
{
var fileSize = fileStream.Length;
golalData.FileBuffer = new byte[fileSize];
fileStream.Read(golalData.FileBuffer, 0, (int)fileSize);
fileStream.Close();
}
if (golalData.FileBuffer == null)
{
throw new Exception("读取了空的文件");
}
IImporter importer = new AsposeExcelImporter();
ExcelData data = importer.Import(golalData.FileBuffer, templatePath);
var errors = new List <CellErrorInfo>();
if (data.Tables.Count == 0)
{
throw new Exception("模版配置不正确");
}
var config = _loadConfigFile();
golalData.UserInfoID = config.GetValue("DefaultUser");
golalData.UserInfoName = config.GetValue("DefaultUserName");
var templateList = JsonHelper.ToList(config.GetValue("TemplateConfig"));
for (int i = 0; i < data.Tables.Count; i++)
{
var sheetTable = data.Tables[i];
if (golalData.Data.ContainsKey(sheetTable.TableName))
{
continue;
}
var tableConfig = templateList.FirstOrDefault(c => c["SheetName"].ToString() == sheetTable.TableName);
if (tableConfig == null)
{
throw new Exception("【" + sheetTable.TableName + "】没有找到相关的配置定义信息，无法导入");
}
var importData = new ImportTable();
importData.SheetName = sheetTable.TableName;
importData.TableName = tableConfig.GetValue("TableName");
importData.SheetData = sheetTable;
if (!golalData.DBList.ContainsKey(tableConfig.GetValue("DB")) || golalData.DBList[tableConfig.GetValue("DB")] == null)
{
throw new Exception("【" + sheetTable.TableName + "】的数据库定义没有找到，请确认链接字符串中有【" + tableConfig.GetValue("DB") + "】的数据库链接配置");
}
#region 初始化字段配置信息（包含EXCEL字段定义和配置文件字段定义)
var requiredFields = tableConfig.GetValue("Required").Split(',');
var uniqueFields = tableConfig.GetValue("Unique").Split(',');
importData.DB = golalData.DBList[tableConfig.GetValue("DB")];
var selectorFields = JsonHelper.ToList(tableConfig.GetValue("SelectorFields"));
foreach (var selectorConfig in selectorFields)
{
var selectorField = selectorConfig.GetValue("FieldName");
if (String.IsNullOrEmpty(selectorField))
{
throw new Exception("【" + sheetTable.TableName + "】关联数据定义中，有字段名为空的配置，请修改配置");
}
if (sheetTable.Structure.Cells.Count(c => c.FieldName == selectorConfig.GetValue("FieldName")) == 0)
{
throw new Exception(String.Format("【{1}】关联数据定义中，字段【{0}】在EXCEL模板中不存在，请检查模板"
, selectorField, sheetTable.TableName));
}
}
foreach (var cellConfig in sheetTable.Structure.Cells)
{
var fieldConfig = new FieldConfig();
fieldConfig.FieldName = cellConfig.FieldName;
if (cellConfig.isDate)
{
fieldConfig.DataType = "DateTime";
}
else if (cellConfig.isDecimal)
{
fieldConfig.DataType = "Decimal";
}
if (!String.IsNullOrEmpty(cellConfig.EnumKey))
{
fieldConfig.IsEnum = true;
fieldConfig.EnumKey = cellConfig.EnumKey;
}
else if (cellConfig.FieldClass == "User")
{
fieldConfig.IsUser = true;
}
else if (cellConfig.FieldClass == "Dept")
{
fieldConfig.IsOrg = true;
}
fieldConfig.IsRequired = requiredFields.Contains(cellConfig.FieldName);
fieldConfig.IsUnique = uniqueFields.Contains(cellConfig.FieldName);
var selector = selectorFields.FirstOrDefault(c => c["FieldName"].ToString() == cellConfig.FieldName);
if (selector != null)
{
fieldConfig.IsRelateField = true;
fieldConfig.RelateFieldName = selector.GetValue("RelateFieldName");
fieldConfig.RelateSheetName = selector.GetValue("SheetName");
fieldConfig.RelateValue = selector.GetValue("ReturnValue");
}
importData.FieldConfigs.Add(fieldConfig);
}
#endregion
string sql = "select '' RowIndex,* from {0}";
importData.ImportDataTable = importData.DB.ExecuteDataTable(String.Format(sql, importData.TableName));
golalData.Data.Add(sheetTable.TableName, importData);
}
return(golalData);
}
catch (Exception exp)
{
LogWriter.Error(exp, exp.Message);
throw exp;
}
}
