public static int getDataInRow <T>(KeyValuePair <string, object> param) { Dictionary <string, string> fileinfo = getFileInfo <T>(); ExcelEdit excel = new ExcelEdit(); Dictionary <string, int> location = getColumn <T>(); string fileName = fileinfo["ExcelName"]; string sheetName = fileinfo["SheetName"]; string str = System.Windows.Forms.Application.StartupPath; if (!File.Exists(str + @"\" + fileName)) { return(0); } excel.Open(str + @"\" + fileName); excel.ws = excel.GetSheet(sheetName); int rows = excel.ws.UsedRange.CurrentRegion.Rows.Count; int crow = 0; for (int i = 2; i < rows + 1; i++) { Range titleRange = excel.ws.Range[excel.ws.Cells[i, location[param.Key]], excel.ws.Cells[i, location[param.Key]]];//选中标题 if (titleRange.Value2 != null) { var gpt = typeof(T).GetProperties(); var mt = gpt.FirstOrDefault(c => c.Name == param.Key); if (mt != null) { object obj = new object(); if (titleRange.Value != null) { Type tp = mt.PropertyType; dynamic value = titleRange.Value; switch (param.Key) { case "CreateDate": tp = typeof(DateTime); value = DateTime.Now; break; } obj = Convert.ChangeType(value, tp); } else { obj = titleRange.Value; } if (obj.Equals(param.Value)) { crow = i; break; } } } } excel.Close(); return(crow); }
/// <summary> /// 回水导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button7_Click(object sender, EventArgs e) { string FileName = ""; SaveFileDialog sfd = new SaveFileDialog(); // sfd.InitialDirectory = "E:\\"; sfd.FileName = "回水统计"; sfd.Filter = "xlsx文件(*.xlsx)|*.xlsx|xls文件(*.xls)|*.xls"; if (sfd.ShowDialog() == DialogResult.OK) { FileName = sfd.FileName; } else { return; } ExcelEdit Edit = new ExcelEdit(); Edit.InsertTable(LVtoTB(listView4), "Sheet1", 2, 1, true); Edit.SaveAs(FileName); Edit.Close(); }
private void executeToExcel(Record record) { string path = Application.StartupPath + ConfigurationManager.AppSettings["excelPath"].ToString(); File.Copy(path, Application.StartupPath + @"\test.xlsx", true); ExcelEdit excelOperator = new ExcelEdit(); excelOperator.Open(Application.StartupPath + @"\test.xlsx"); Microsoft.Office.Interop.Excel.Worksheet sheet1 = excelOperator.GetSheet("Sheet1"); sheet1.Cells[4, 5].Value = record.Operator; sheet1.Cells[4, 8].Value = record.CreateTime.ToString("yyyy-MM-dd"); sheet1.Cells[6, 2].Value = record.SeriesNum; sheet1.Cells[6, 5].Value = record.ProName; sheet1.Cells[6, 8].Value = record.Result == 1 ? "合格" : "不合格"; for (int i = 0; i < record.RecordDetailList.Count; i++) { sheet1.Cells[i + 11, 2].Value = record.RecordDetailList[i].Index; sheet1.Cells[i + 11, 3].Value = record.RecordDetailList[i].TestValue + " N·m"; sheet1.Cells[i + 11, 4].Value = record.RecordDetailList[i].Standard + " N·m"; sheet1.Cells[i + 11, 5].Value = record.RecordDetailList[i].Difference; sheet1.Cells[i + 11, 6].Value = record.RecordDetailList[i].Percent + "%"; sheet1.Cells[i + 11, 7].Value = record.Result == 1 ? "合格" : "不合格"; sheet1.Cells[i + 11, 8].Value = record.RecordDetailList[i].Upper + " N·m"; sheet1.Cells[i + 11, 9].Value = record.RecordDetailList[i].Lower + " N·m"; } if (ConfigurationManager.AppSettings["printMode"].ToString().Equals("preview")) { sheet1.PrintPreview(); } else if (ConfigurationManager.AppSettings["printMode"].ToString().Equals("print")) { sheet1.PrintOutEx(); } excelOperator.Save(); excelOperator.Close(); GC.Collect(); }
private void ProcessExcel() { Log("导出Excel文件..."); ExcelEdit ed = new ExcelEdit(); ed.Create(); int index = 1; ed.SetCellValue("Sheet1", index, 1, "图序号范围"); ed.SetCellValue("Sheet1", index, 2, "面料编号"); ed.SetCellValue("Sheet1", index, 3, "面料名称"); ed.SetCellValue("Sheet1", index, 4, "总米数"); ed.SetCellValue("Sheet1", index, 5, "实际米数"); index++; // 面料名称、面料编号、规则名列表、文件路径列表、米数列表、图号列表 foreach (var item in FacePathTuples) { if (item.Item3.Count == 0) { continue; } string strnumrange = item.Item6.Count >= 2 ? item.Item6.First() + "-" + item.Item6.Last() : item.Item6[0]; ed.SetCellValue("Sheet1", index, 1, strnumrange); double totalmeter = 0.0; foreach (var fi in item.Item5) { totalmeter += fi; } ed.SetCellValue("Sheet1", index, 2, item.Item2); ed.SetCellValue("Sheet1", index, 3, item.Item1); ed.SetCellValue("Sheet1", index, 4, totalmeter); ed.SetCellValue("Sheet1", index, 5, item.Item5.Count * Ratio + totalmeter); index++; } string excelname = Url.Substring(Url.LastIndexOf('\\') + 1); ExcelUrl = Url + "\\" + excelname + ".xlsx"; ed.SaveAs(ExcelUrl); ed.Close(); Log("导出Excel文件结束"); if (MessageBox.Show("是否更新库存表?", "更新", MessageBoxButton.OKCancel) == MessageBoxResult.OK) { bool b0 = System.IO.File.Exists(ExcelUrl); if (!b0) { Log("找不到样表文件" + ExcelUrl + "更新失败"); return; } string stockexcel = Url + "\\库存.xlsx"; bool b2 = System.IO.File.Exists(stockexcel); if (!b2) { Log("找不到库存表文件" + stockexcel + "更新失败"); return; } try { if (!CreateStock.Create(stockexcel, ExcelUrl, "")) { Log("更新库存表失败!请检查文件内容有效性!"); } else { Log("更新库存表成功!"); } } catch (Exception) { Log("更新库存表失败!请检查文件内容有效性!"); } } }
public void Process(int startindex, float ratio) { StartIndex = startindex; Ratio = ratio; Log("查找文件..."); List <string> files = new List <string>(); GetAllFiles(Url, ref files); if (files.Count == 0) { Log("没有找到文件,结束"); return; } Log("查找小样文件..."); List <FileInfo> fileinfos = new List <FileInfo>(); int totalindex = StartIndex; foreach (var f in files) { FileInfo fi; if (ParseFileName(Path.GetFileNameWithoutExtension(f), out fi)) { fi.Url = f; fi.Number = totalindex.ToString() + "号"; fileinfos.Add(fi); totalindex++; } } if (fileinfos.Count == 0) { Log("没有找到米样文件,结束"); return; } Log("找到" + fileinfos.Count.ToString() + "个小样文件,开始重命名..."); foreach (var item in fileinfos) { string newname = item.Number + "-" + item.ReguName + Path.GetExtension(item.Url); Rename(item.Url, newname); } Log("重命名结束..."); Log("导出Excel文件..."); { ExcelEdit ed = new ExcelEdit(); ed.Create(); int index = 1; ed.SetCellValue("Sheet1", index, 1, "图号"); ed.SetCellValue("Sheet1", index, 2, "描述"); index++; foreach (var item in fileinfos) { ed.SetCellValue("Sheet1", index, 1, item.Number); string disc = item.Format + item.Disc + "-"; foreach (var fn in item.FaceNameCount) { disc += fn.Item1.Item1 + "(" + fn.Item2.ToString() + ")个、"; } disc.TrimEnd('、'); ed.SetCellValue("Sheet1", index, 2, disc); index++; } string excelname = Url.Substring(Url.LastIndexOf('\\') + 1); ExcelUrl1 = Url + "\\小样" + excelname + "1.xlsx"; ed.SaveAs(ExcelUrl1); ed.Close(); } { int nCount = Config.Settings.lstFaceInfo.Count; List <Tuple <string, string, List <int> > > sortfacenamecount = new List <Tuple <string, string, List <int> > >(); for (int i = 0; i < nCount; i++) { sortfacenamecount.Add(new Tuple <string, string, List <int> >(Config.Settings.lstFaceInfo[i].Index.ToString() + "#", Config.Settings.lstFaceInfo[i].FaceName, new List <int>())); } foreach (var item in fileinfos) { foreach (var fn in item.FaceNameCount) { var res = sortfacenamecount.Where(s => s.Item1 == fn.Item1.Item2); if (res.Count() != 0) { res.ElementAt(0).Item3.Add(fn.Item2); } else { Log("文件" + item.Url + "的面料类型" + fn.Item1 + "没有定义"); } } } ExcelEdit ed = new ExcelEdit(); ed.Create(); int index = 1; ed.SetCellValue("Sheet1", index, 1, "面料编号"); ed.SetCellValue("Sheet1", index, 2, "面料名称"); ed.SetCellValue("Sheet1", index, 3, "个数"); ed.SetCellValue("Sheet1", index, 4, "实际米数"); index++; foreach (var item in sortfacenamecount) { if (item.Item3.Count == 0) { continue; } ed.SetCellValue("Sheet1", index, 1, item.Item1); ed.SetCellValue("Sheet1", index, 2, item.Item2); int totalcount = 0; foreach (var fn in item.Item3) { totalcount += fn; } ed.SetCellValue("Sheet1", index, 3, totalcount); ed.SetCellValue("Sheet1", index, 4, totalcount * Ratio); index++; } string excelname = Url.Substring(Url.LastIndexOf('\\') + 1); ExcelUrl2 = Url + "\\小样" + excelname + "2.xlsx"; ed.SaveAs(ExcelUrl2); ed.Close(); } Log("导出Excel文件结束"); { if (MessageBox.Show("是否更新库存表?", "更新", MessageBoxButton.OKCancel) == MessageBoxResult.OK) { bool b0 = System.IO.File.Exists(ExcelUrl2); if (!b0) { Log("找不到小样表文件" + ExcelUrl2 + ",更新失败"); return; } string stockexcel = Url + "\\库存.xlsx"; bool b2 = System.IO.File.Exists(stockexcel); if (!b2) { Log("找不到库存表文件" + stockexcel + "更新失败"); return; } try { if (!CreateStock.Create(stockexcel, "", ExcelUrl2)) { Log("更新库存表失败!请检查文件内容有效性!"); } else { Log("更新库存表成功!"); } } catch (Exception) { Log("更新库存表失败!请检查文件内容有效性!"); } } } }
private void saveBtn_Click(object sender, EventArgs e)//YES { excelEdit.SaveAs("F:\\BaiduYunDownload\\Excel操作\\example.xlsx"); excelEdit.Close(); }
/// <summary> /// 从文件获取数据集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <returns></returns> public static void getListFromFile <T>() { string className = typeof(T).Name; Dictionary <string, string> fileinfo = getFileInfo <T>(); string fileName = fileinfo["ExcelName"]; string sheetName = fileinfo["SheetName"]; List <T> Sourcelist = new List <T>(); if (!PubulicData.sourceData.Keys.Contains(className)) { PubulicData.sourceData.Add(className, Sourcelist); } else { PubulicData.sourceData[className] = Sourcelist; } ExcelEdit excel = new ExcelEdit(); Dictionary <string, int> location = getColumn <T>(); string str = System.Windows.Forms.Application.StartupPath; if (!File.Exists(str + @"\" + fileName)) { return; } excel.Open(str + @"\" + fileName); excel.ws = excel.GetSheet(sheetName); int rows = excel.ws.UsedRange.CurrentRegion.Rows.Count; for (int i = 2; i < rows + 1; i++) { T bd = Activator.CreateInstance <T>();; foreach (string j in location.Keys) { Range titleRange = excel.ws.Range[excel.ws.Cells[i, location[j]], excel.ws.Cells[i, location[j]]];//选中标题 if (titleRange.Value2 != null) { var gpt = typeof(T).GetProperties(); var mt = gpt.FirstOrDefault(c => c.Name == j); if (mt != null) { object obj = new object(); if (titleRange.Value != null) { Type tp = mt.PropertyType; dynamic value = titleRange.Value; switch (j) { case "CreateDate": tp = typeof(DateTime); value = DateTime.Now; break; } obj = Convert.ChangeType(value, tp); } else { obj = titleRange.Value; } mt.SetValue(bd, obj); } } } Sourcelist.Add(bd); } excel.Close(); }
/// <summary> /// 保存数据到文件 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sourceData"></param> /// <param name="fileName"></param> /// <param name="sheetName"></param> public static void saveDataToFile <T>(List <T> sourceData) { Dictionary <string, string> fileinfo = getFileInfo <T>(); string fileName = fileinfo["ExcelName"]; string sheetName = fileinfo["SheetName"]; ExcelEdit excel = new ExcelEdit(); string str = System.Windows.Forms.Application.StartupPath; Worksheet ws = null; if (!Directory.Exists(str)) { Directory.CreateDirectory(str); } if (!File.Exists(str + @"\" + fileName)) { excel.mFilename = str + @"\" + fileName; excel.Create(); excel.AddSheet(sheetName); } else { excel.Open(str + @"\" + fileName); } if (excel.GetSheet(sheetName) == null) { ws = excel.AddSheet(sheetName); } else { ws = excel.GetSheet(sheetName); } int y = 2; foreach (T item in sourceData) { int x = 1; foreach (PropertyInfo obj in item.GetType().GetProperties()) { object value = null; if (y == 2) { value = obj.Name.ToString(); excel.SetCellValue(ws, 1, x, value); } value = item.GetType().GetProperty(obj.Name.ToString()).GetValue(item); Range titleRange = ws.Range[ws.Cells[y, x], ws.Cells[y, x]]; //选中标题 titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中 if (titleRange.Value != null && value != null && value.Equals(titleRange.Value)) { x++; continue; } else { excel.SetCellValue(ws, y, x, value); x++; } } y++; } excel.wb.RefreshAll(); if (File.Exists(str + @"\" + fileName)) { excel.Save(); } else { excel.SaveAs(str + @"\" + fileName); } excel.Close(); }