private async void ReadExcelFileInfo(string path)
{
try
{
string ext = System.IO.Path.GetExtension(path).ToLower();
if (!(ext == ".xls" || ext == ".xlsx" || ext == ".xlsm"))
{
throw new Exception($"Это не файл Excel! Расширение - {ext}.\n{path}");
}
ExcelFileInfo result = await Task <ExcelFileInfo> .Factory.StartNew(() =>
{
using (Office.UsingExcel excel = new Office.UsingExcel())
return(excel.ReadExcelFileInfo(path));
});
this.OpenedExcelFile = result;
}
catch (Exception ex)
{
string message = ex.Message;
if (ex.InnerException != null)
{
message += $"\n{ex.InnerException.Message}";
message += $"\nStackTrace:\n{ex.InnerException.StackTrace}";
}
MessageBox.Show(message, "Ошибка чтения Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.OpenedExcelFile = null;
}
}
public MainForm()
{
InitializeComponent();
this.Icon = Properties.Resources.Hard_Disk_Server_icon1;
OpenedExcelFile = null;
btnQuickLoad.Enabled = FTPConnectionSettings.LoadSettings() != null;
FTPConnectionSettings.FTPSettingsChanged += FTPConnectionSettings_FTPSettingsChanged;
}
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="creator"></param>
/// <returns></returns>
public static ExcelFileInfo CreateExcelPackage(string fileName, Action <ExcelPackage> creator)
{
var file = new ExcelFileInfo(fileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
using (var excelPackage = new ExcelPackage())
{
creator(excelPackage);
Save(excelPackage, file);
}
return(file);
}
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="creator"></param>
/// <returns></returns>
public static ExcelFileInfo CreateExcelPackage(string fileName, Action <ExcelPackage> creator)
{
var file = new ExcelFileInfo(fileName, _filetype);
using (var excelPackage = new ExcelPackage())
{
creator(excelPackage);
Save(excelPackage, file);
}
return(file);
}
/// <summary>
/// 生成Excel导入模板
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileName"></param>
/// <returns></returns>
/// <exception cref="ArgumentException">文件名必须填写! - fileName</exception>
public Task <ExcelFileInfo> GenerateTemplate <T>(string fileName) where T : class
{
if (string.IsNullOrWhiteSpace(fileName))
{
throw new ImportException("文件名必须填写!");
}
ExcelFileInfo fileInfo = ExcelHelper.CreateExcelPackage(fileName, excelPackage =>
{
StructureExcel <T>(excelPackage);
});
return(Task.FromResult(fileInfo));
}
public void CanCorrectlyImportXlsFiles()
{
var excelFileInfo = new ExcelFileInfo(new FileInfo(@"Excel\Resources\People.xls"));
var mapping = new MappingRules <Person, int>();
mapping.AddMapping(0, p => p.Name);
mapping.AddMapping(1, p => p.FriendsCount);
mapping.AddMapping(2, p => p.DateOfBirth);
mapping.AddMapping(3, p => p.Sex, value => Enum.Parse(typeof(Gender), value as string));
var dataLoader = new ExcelDataLoader <Person>(excelFileInfo, mapping);
var result = dataLoader.LoadData();
Assert.IsNotNull(result);
Assert.AreEqual(4, result.Count());
}
private void ReadExcelFileInfo(string path)
{
ExcelFileInfo fileInfo = null;
try
{
using (UsingExcel xls = new UsingExcel())
{
fileInfo = xls.ReadExcelFileInfo(path);
}
}
catch (Exception ex)
{
ShowErrorMessage(ex, "Ошибка чтения Excel");
}
this.OpenedExcelFile = fileInfo;
}
private void Init()
{
this.treeViewExcel.Nodes.Clear();
this.dgvJob.DataSource = new DataTable();
List <ExcelFileInfo> excelFileList = new List <ExcelFileInfo>();
Regex regex = new Regex("null_\\d+_\\d+_\\d+.*\\.xls", RegexOptions.IgnoreCase);
foreach (string excelFile in
Directory.EnumerateFiles(Path_Excel, "*.xls"))
{
//符合要求的文件
if (regex.IsMatch(excelFile) &&
File.GetCreationTime(excelFile) >= DateTime.Now.AddMonths(-1))
{
excelFileList.Add(new ExcelFileInfo(excelFile));
}
}
//按从晚到早的时间排序
for (int i = 0; i < excelFileList.Count - 1; i++)
{
for (int j = i + 1; j <= excelFileList.Count - 1; j++)
{
if (excelFileList[i].CreateTime < excelFileList[j].CreateTime)
{
ExcelFileInfo tempExcel = excelFileList[i];
excelFileList[i] = excelFileList[j];
excelFileList[j] = tempExcel;
}
}
}
//保留最近的15项
int lastNum = 15;
if (excelFileList.Count > lastNum)
{
excelFileList.RemoveRange(lastNum, excelFileList.Count - lastNum);
}
//将排序好的显示在treeView中
foreach (ExcelFileInfo excelFile in excelFileList)
{
TreeNode tn = new TreeNode(
Path.GetFileNameWithoutExtension(excelFile.FileFullName));
tn.Tag = excelFile;
this.treeViewExcel.Nodes.Add(tn);
}
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
if (this.BWorker != null && BWorker.IsBusy)
{
MessageBox.Show("Дождитесь завершения операции.", "Программа в процессе..", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string path = ExcelStatic.OpenExcelFileDialog();
if (!string.IsNullOrEmpty(path))
{
ReadExcelFileInfo(path);
}
else
{
OpenedExcelFile = null;
}
}
public JsonResult SaveFile(HttpPostedFileBase SubmitFile, string EntityClassName)
{
#region 只知道类型名，调用组件实现导入
#region 通过组件保存文件
var fileName = SubmitFile.FileName;
var fileContent = new byte[SubmitFile.ContentLength];
SubmitFile.InputStream.Read(fileContent, 0, SubmitFile.ContentLength);
var submitExcelInfo = new ExcelFileInfo(fileName, EntityClassName, fileContent);
var saveExcelHelper = new SaveExcelHelper(submitExcelInfo);
var saveFileInfo = saveExcelHelper.SaveFile();
#endregion
var returnNewObj = new { FileName = saveFileInfo.FileName, EntityClassName = EntityClassName };
return(Json(returnNewObj));
#endregion
}
//private void tsmiChuBan_Click(object sender, EventArgs e)
//{
// //获取选择到的列和行
// List<int> selectRows= new List<int>();
// // List<int> selectColumns = new List<int>();
// foreach (DataGridViewCell cell in this.dgvJob.SelectedCells)
// {
// if (!selectRows.Contains(cell.RowIndex))
// {
// selectRows.Add(cell.RowIndex);
// }
// //if (!selectColumns.Contains(cell.ColumnIndex))
// //{
// // selectColumns.Add(cell.ColumnIndex);
// //}
// }
// List <string> sqlList=new List<string>() ;
// foreach (int selectRowIndex in selectRows)
// {
// DataGridViewRow dgvr = this.dgvJob.Rows[selectRowIndex];
// string InsertString = "INSERT INTO [JobPublished]([gdh],[sjjt],"
// + "[khjc],[cpmc],[zzcc],[mzcc],[ss1],[ss2],[sbs],[bz],[yk],"
// + "[ExcelFile],[Published])VALUES("
// +"'" +this.dgvJob["ColumnGdh", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnSjjt", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnKhjc", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnCpmc", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnZzcc", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnMzcc", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnSs1", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnSs2", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnSbs", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnBz", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnYk", selectRowIndex].Value.ToString() + "',"
// + "'" + this.dgvJob["ColumnExcelFile", selectRowIndex].Value.ToString() + "',"
// + 1+");";
// sqlList.Add(InsertString);
// }
// SQLiteList.YbfSQLite.ExecuteSqlTran(sqlList);
//}
private void tsmiStatistics_Click(object sender, EventArgs e)
{
List <DataTable> dtList = new List <DataTable>();
foreach (TreeNode item in this.treeViewExcel.Nodes)
{
try
{
if (item.Checked)
{
ExcelFileInfo excel = item.Tag as ExcelFileInfo;
DataTable dt = Comm_Method.GetPublishDataTableFromExcelFile(excel.FileFullName);
if (dt != null && dt.Rows.Count > 0)
{
dtList.Add(dt);
}
}
}
catch (Exception)
{
continue;
}
}
if (dtList.Count > 0)
{
DataTable dtAll = dtList[0].Clone();
foreach (DataTable item in dtList)
{
dtAll.Merge(item);
}
//分类汇总
Dictionary <string, int> dicCount = new Dictionary <string, int>();
foreach (DataRow row in dtAll.Rows)
{
try
{
string jitai = dic_jitai[row["上机机台"].ToString()];
int shuliang = Convert.ToInt32(row["晒版数"]);
if (dicCount.ContainsKey(jitai))
{
dicCount[jitai] += shuliang;
}
else
{
dicCount.Add(jitai, shuliang);
}
}
catch (Exception)
{
continue;
}
}
//显示数量
StringBuilder sb = new StringBuilder();
int sum = 0;
foreach (string key in dicCount.Keys)
{
sb.AppendLine(key + ":" + dicCount[key]);
sum += dicCount[key];
}
if (sum > 0 && sb.Length > 0)
{
sb.AppendLine("------------");
sb.AppendLine(" 共计：" + sum);
}
MessageBox.Show(sb.ToString());
}
else
{
return;
}
}
private static void Save(ExcelPackage excelPackage, ExcelFileInfo file)
{
excelPackage.SaveAs(new FileInfo(file.FileName));
}
private void tsmiShow_Click(object sender, EventArgs e)
{
this.dgvJob.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
this.dgvJob.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
//this.dgvJob.Rows.Clear();
List <DataTable> dtList = new List <DataTable>();
foreach (TreeNode item in this.treeViewExcel.Nodes)
{
if (item.Checked)
{
ExcelFileInfo excel = item.Tag as ExcelFileInfo;
DataTable dt = Comm_Method.GetPublishDataTableFromExcelFile(excel.FileFullName);
if (dt != null && dt.Rows.Count > 0)
{
dtList.Add(dt);
}
}
}
if (dtList.Count < 1)
{
return;
}
// AddToDgv(dtList);
//**********
DataTable dtAll = dtList[0].Clone();
foreach (DataTable item in dtList)
{
dtAll.Merge(item);
}
//删除重复的数据
for (int i = dtAll.Rows.Count - 1; i > 0; i--)
{
bool isCome = true;
foreach (DataRow row in dtAll.Rows)
{
foreach (DataColumn column in dtAll.Columns)
{
if (!dtAll.Rows[i][column].ToString().Equals(row[column].ToString()))
{
isCome = false;
break;
}
}
if (isCome)
{
break;
}
}
if (isCome)
{
dtAll.Rows.RemoveAt(i);
}
}
this.dgvJob.DataSource = dtAll;
this.dgvJob.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
this.dgvJob.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;
}
public ActionResult ProcessResult(string Msg, string FileName, string EntityClassName)
{
var excelFileInfo = new ExcelFileInfo(FileName, EntityClassName);
return(View("UploadResult", excelFileInfo));
}
public ActionResult Upload(string TemplateFileName, string EntityClassName)
{
var tempFileInfo = new ExcelFileInfo(string.Empty, TemplateFileName, EntityClassName);
return(View(tempFileInfo));
}
public ActionResult SubmitUpload(HttpPostedFileBase SubmitFile, string EntityClassName)
{
#region 已知类型调用组件实现导入
/*var fileName = DateTime.Now.ToString("yyyMMddHHmmss") + SubmitFile.FileName;
* var filePath = Request.MapPath("~/UploadFiles");
* var saveFilePathAndName = Path.Combine(filePath, fileName);
*
* SubmitFile.SaveAs(saveFilePathAndName);
*
* var type = Type.GetType("TestDataSourceModel");
*
#region 通过组件获取实体
*
* var fileInfo = new ExcelFileInfo(filePath,fileName);
*
* var errorEntities = new List<TestDataSourceModel>();
*
* var stopWatch = new Stopwatch();
* stopWatch.Start();
* var validEntities= ExcelToEntityHelper.GetEntities<TestDataSourceModel>(fileInfo, out errorEntities);
* stopWatch.Stop();
#endregion
*
#region 通过组件持久化实体
* var stopWatch1 = new Stopwatch();
* stopWatch1.Start();
* EntityToDBHelper.ImportEntitiesWidthBulkCopy<TestDataSourceModel>(validEntities);
* stopWatch1.Stop();
#endregion
*
#region 通过组件生成导入错误文件
* var errorFileInfo = ErrorEntityToFileHelper.GetFile<TestDataSourceModel>(errorEntities);
#endregion
*
* return View("UploadResult", errorFileInfo);*/
#endregion
#region 只知道类型名，调用组件实现导入
#region 通过组件保存文件
var fileName = SubmitFile.FileName;
var fileContent = new byte[SubmitFile.ContentLength];
SubmitFile.InputStream.Read(fileContent, 0, SubmitFile.ContentLength);
var submitExcelInfo = new ExcelFileInfo(fileName, EntityClassName, fileContent);
var saveExcelHelper = new SaveExcelHelper(submitExcelInfo);
var saveFileInfo = saveExcelHelper.SaveFile();
#endregion
#region 通过组件获取实体
var errorEntities = new List <EntityBase>();
var validEntities = ExcelToEntityHelper.GetEntities(saveFileInfo.EntityType, saveFileInfo, out errorEntities);
#endregion
#region 通过组件持久化实体
EntityToDBHelper.ImportEntitiesWidthBulkCopy(saveFileInfo.EntityType, validEntities);
#endregion
#region 通过组件生成导入错误文件
var errorFileInfo = ErrorEntityToFileHelper.WriteFile(saveFileInfo.EntityType, errorEntities);
#endregion
return(View("UploadResult", errorFileInfo));
#endregion
}
public ActionResult UploadResult()
{
var excelFileInfo = new ExcelFileInfo(string.Empty, string.Empty);
return(View(excelFileInfo));
}
public void ProcessFile(string FileName, string EntityClassName)
{
#region 通过文件名、类型获取文件信息
var saveFileInfo = SaveExcelHelper.GetExcelFileInfo(EntityClassName, FileName);
#endregion
#region 通过组件获取实体
var errorEntities = new List <EntityBase>();
var validEntities = ExcelToEntityHelper.GetEntities(saveFileInfo.EntityType, saveFileInfo, out errorEntities);
#endregion
#region 分批持久化数据并反馈进度
var msg = string.Empty;
var processInfo = BatchProcessUtility.GetProcessInfo(validEntities.Count, 10);
for (int i = 0; i < processInfo.TotalPage; i++)
{
try
{
var batchEntities = validEntities.Skip(i * processInfo.PageRecord).Take(processInfo.PageRecord).ToList();
var currentBatchCount = batchEntities.Count();
#region 通过组件持久化实体
EntityToDBHelper.ImportEntitiesWidthBulkCopy(saveFileInfo.EntityType, batchEntities);
#endregion
if (processInfo.IsNewPersent(i + 1, currentBatchCount))
{
Response.Write("<script>parent.fUpdateProcess(" + processInfo.CurrentPersent + "," + processInfo.NeedSeconds + ")</script>");
Response.Flush();
}
}
catch (Exception ex)
{
msg = "处理完成，但处理过程中发生异常：" + ex.Message + ex.StackTrace;
}
}
#endregion
#region 通过组件生成导入错误文件
var errorFileInfo = new ExcelFileInfo(string.Empty, EntityClassName);
if (errorEntities.Count > 0)
{
errorFileInfo = ErrorEntityToFileHelper.WriteFile(saveFileInfo.EntityType, errorEntities);
}
#endregion
#region 反馈最后的导入结果
if (string.IsNullOrEmpty(msg))
{
msg = "处理完成";
}
Response.Write("<script>parent.fProcessResult('" + msg + "','" + errorFileInfo.FileName + "','" + errorFileInfo.EntityClassName + "')</script>");
Response.Flush();
#endregion
}
private void tsmiBaoBiao_Click(object sender, EventArgs e)
{
char[] InvalidFileNameChars = Path.GetInvalidFileNameChars();
string[] pdfFiles = Directory.GetFiles(@"\\128.1.30.144\JobData\pdf\已下单PDF"
, "*.pdf", SearchOption.AllDirectories);
List <DataTable> dtList = new List <DataTable>();
foreach (TreeNode item in this.treeViewExcel.Nodes)
{
if (item.Checked)
{
ExcelFileInfo excel = item.Tag as ExcelFileInfo;
DataTable dt = Comm_Method.GetPublishDataTableFromExcelFile(excel.FileFullName);
if (dt != null && dt.Rows.Count > 0)
{
dtList.Add(dt);
}
}
}
if (dtList.Count > 0)
{
DataTable dtAll = dtList[0].Clone();
foreach (DataTable item in dtList)
{
dtAll.Merge(item);
}
//剔除旧版
DataTable dt_baobiao = new DataTable();
dt_baobiao.Columns.Add("日期");
dt_baobiao.Columns.Add("产品名称");
dt_baobiao.Columns.Add("制造尺寸");
dt_baobiao.Columns.Add("面纸尺寸");
dt_baobiao.Columns.Add("输出颜色");
dt_baobiao.Columns.Add("网线");
dt_baobiao.Columns.Add("机台");
dt_baobiao.Columns.Add("咬口");
dt_baobiao.Columns.Add("备注");
foreach (DataRow row in dtAll.Select("备注<>'旧版'"))
{
DataRow newRow = dt_baobiao.NewRow();
newRow["日期"] = DateTime.Now.AddHours(-6).ToString("yyyy-MM-dd");
//newRow["产品名称"] = row["客户简称"].ToString()+row["产品名称"].ToString();
// newRow["产品名称"] = pdfFiles.First<string>(pp => pp.Contains(row["产品名称"].ToString()));
newRow["制造尺寸"] = row["制造尺寸"].ToString().Replace('x', '*');
newRow["面纸尺寸"] = row["面纸尺寸"].ToString().Replace('x', '*');
newRow["网线"] = "175";
newRow["机台"] = dic_jitai[row["上机机台"].ToString()];
newRow["咬口"] = "";
newRow["备注"] = "";
//***确认产品名称
string cpmc = row["产品名称"].ToString().Replace('*', 'x');
bool cunzai = false;
foreach (string pdffile in pdfFiles)
{
foreach (char Invalid in InvalidFileNameChars)
{
cpmc = cpmc.Replace(Invalid, '-');
}
if (pdffile.Contains(cpmc))
{
cpmc = Path.GetFileNameWithoutExtension(pdffile);
cunzai = true;
break;
}
}
if (cunzai)
{
newRow["产品名称"] = cpmc;
}
else
{
newRow["产品名称"] = "【【" + cpmc + "】】";
}
//输出颜色
string scys = "";
string ss1 = row["色数1"].ToString();
string ss2 = row["色数2"].ToString();
if (ss1 == "4")
{
scys = "CMYK";
}
if (!string.IsNullOrWhiteSpace(ss2))
{
if (ss2 == "1")
{
scys += (string.IsNullOrWhiteSpace(ss1)?"专":"+专");
}
else
{
string zs = string.Format("{0}专", ss2);
scys += (string.IsNullOrWhiteSpace(ss1) ? zs : "+" + zs);
}
}
newRow["输出颜色"] = scys;
//进行AB面完全一致的判断
bool isAB = false;
foreach (DataRow row_Baobiao in dt_baobiao.Rows)
{
isAB = IsABside(row_Baobiao, newRow);
if (isAB)
{
if (row_Baobiao["输出颜色"].ToString().Contains('+'))
{
row_Baobiao["输出颜色"] = "(" + row_Baobiao["输出颜色"] + ")*2";
}
else
{
row_Baobiao["输出颜色"] = row_Baobiao["输出颜色"] + "*2";
}
row_Baobiao["产品名称"] = row_Baobiao["产品名称"].ToString().Replace("B面", "A面").Replace("b面", "A面");
break;
}
}
if (isAB)
{
continue;
}
dt_baobiao.Rows.Add(newRow);
}
new FormBaoBiao(dt_baobiao).ShowDialog();
}
else
{
return;
}
}
public static ExcelFileInfo ReadExcel(string excelFile)
{
var result = new ExcelFileInfo();
try
{
var connString = string.Format(excelConnection, excelFile);
using (var objConn = new OleDbConnection(connString))
{
objConn.Open();
using (var schema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
if (schema == null)
{
return(null);
}
result.SheetNames = new string[schema.Rows.Count];
// Add the sheet name to the string array.
for (int j = 0; j < schema.Rows.Count; j++)
{
var key = schema.Rows[j]["TABLE_NAME"].ToString();
if (key.EndsWith("'"))
{
key = key.Remove(key.Length - 1, 1);
}
if (key.StartsWith("'"))
{
key = key.Remove(0, 1);
}
if (key.EndsWith("$"))
{
key = key.Remove(key.Length - 1, 1);
}
result.SheetNames[j] = key;
}
}
if (result.SheetNames.Length == 0)
{
return(result);
}
using (var cmd = objConn.CreateCommand())
using (var adapter = new OleDbDataAdapter(cmd))
using (var ds = new DataSet())
{
cmd.CommandText = "SELECT Top 1 * FROM [" + result.SheetNames[0] + "$]";
adapter.Fill(ds);
var columns = new List <string>();
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
}
}
result.SheetColumns = columns.ToArray();
}
}
return(result);
}
catch (OleDbException)
{
throw;
}
catch (IOException)
{
throw;
}
catch (Exception)
{
return(null);
}
}
