private void CmdImportExecute() { OpenFileDialog ofd = new OpenFileDialog(); ofd.Filter = "Excel Files(*.xls,*.xlsx)|*.xls;*.xlsx"; ofd.Multiselect = false; bool? result = ofd.ShowDialog(); if(result.HasValue && result.Value) { #region Step1:读取Excel表格 string file = ofd.FileName; ExcelImporter importer = new ExcelImporter(); DataTable dt = null; try { dt = importer.Import(ofd.FileName, "Sheet1"); } catch (Exception e) { Trace.TraceError(string.Format(@"{0},{1}", DateTime.Now.ToString(), e.Message)); MessageBox.Show("读取Excel出错!"); return; } #endregion #region Step2:解析Excel表格中数据 List<SKUCGY> cgys; try { cgys = this.GenerateSKUCGYList(dt); } catch (Exception e) { MessageBox.Show("解析Excel数据失败!"); return; } #endregion if (cgys != null && cgys.Count > 0) { #region Step3:判断表格数据中编码字段是否与数据库中已有编码冲突 #endregion #region Step4:将解析出来的一级数据保存至服务器 try { _oneMangement.ImportOnes(cgys); ApplicationContext.EventAggregator.GetEvent<OneChangedEvent>().Publish(null); this.Refresh(); } catch (Exception e) { Trace.TraceError(string.Format(@"{0},{1}", DateTime.Now.ToString(), e.Message)); MessageBox.Show("导入一级数据出错!"); } #endregion } } }
private void CmdImportExecute() { if (this.SelectedSKUCGY == null || this.SelectedSKUCGY.LevelIndex !=2 || this.SelectedAttType == null) { MessageBox.Show("请选择二级以及属性类别再行导入!"); return; } OpenFileDialog ofd = new OpenFileDialog(); ofd.Filter = "Excel Files(*.xls,*.xlsx)|*.xls;*.xlsx"; ofd.Multiselect = false; bool? result = ofd.ShowDialog(); if (result.HasValue && result.Value) { #region Step1:读取Excel表格 string file = ofd.FileName; ExcelImporter importer = new ExcelImporter(); DataTable dt = null; try { dt = importer.Import(ofd.FileName, "SheetATT"); } catch (Exception e) { Trace.TraceError(string.Format(@"{0},{1}", DateTime.Now.ToString(), e.Message)); MessageBox.Show("读取Excel出错!"); return; } #endregion #region Step2:解析Excel表格中数据 List<SKUATT> atts; try { atts = this.GenerateSKUATTList(dt); } catch (Exception e) { MessageBox.Show("解析Excel数据失败!"); return; } #endregion if (atts != null && atts.Count > 0) { #region Step3:判断表格数据中编码字段是否与数据库中已有编码冲突 #endregion #region Step4:将解析出来的一级数据保存至服务器 try { _bll.ImportATTs(atts); this.Refresh(); } catch (Exception e) { Trace.TraceError(string.Format(@"{0},{1}", DateTime.Now.ToString(), e.Message)); MessageBox.Show("导入属性数据出错!"); } #endregion } } }
static void Main(string[] args) { string dir = AppDomain.CurrentDomain.BaseDirectory; string templatePath = string.Format(@"{0}{1}", dir, "账目出入.xlsx"); #region 导入测试 ExcelImporter importer = new ExcelImporter(); DataTable dt = null; try { dt = importer.Import(templatePath, "Sheet 1"); } catch(Exception e) { return; } List<Entity> list = new List<Entity>(); Entity entity; foreach(DataRow dr in dt.Rows) { entity = new Entity() { Code = dr.Field<string>("单位编号"), Name = dr.Field<string>("单位名称"), }; string account = dr.Field<string>("应付余额"); decimal Account; decimal.TryParse(account, out Account); entity.Account = Account; list.Add(entity); } #endregion #region 导出测试 //ExcelUtil util = new ExcelUtil(templatePath); //using (ExcelUtil util = new ExcelUtil(templatePath)) //{ // //util.SetCellValue(2, 1, "0810311211"); // //util.SetCellValue(2, 2, "郭坤"); // //util.SetFont("楷体_GB2312", 20, true); // //util.SetCellValue(2, 3, "男"); // //util.SetCellValue(2, 4, 25); // //util.RowAutoFit(2, 4); // //util.ColumnAutoFit(1, 4); // for (int i = 2; i <= 10;i++ ) // { // util.SetCellValue(i, 1, "学号" + (i - 1).ToString()); // util.SetCellValue(i, 2, "姓名" + (i - 1).ToString()); // util.SetCellValue(i, 3, "性别" + (i - 1).ToString()); // util.SetCellValue(i, 4, "年龄" + (i - 1).ToString()); // } // util.SetRange(2, 1, 10, 4); // util.SetAliment(Excel.XlHAlign.xlHAlignCenter, Excel.XlVAlign.xlVAlignCenter); // //util.SetBorders(Excel.XlLineStyle.xlSlantDashDot); // util.SetLeftBorder(Excel.XlLineStyle.xlContinuous); // string exportPath = string.Format(@"{0}{1}", dir, "1.xlsx"); // util.SaveAs(exportPath); //} //using (ExcelExporter util = new ExcelExporter()) //{ // //util.SetCellValue(2, 1, "0810311211"); // //util.SetCellValue(2, 2, "郭坤"); // //util.SetFont("楷体_GB2312", 20, true); // //util.SetCellValue(2, 3, "男"); // //util.SetCellValue(2, 4, 25); // //util.RowAutoFit(2, 4); // //util.ColumnAutoFit(1, 4); // for (int i = 1; i <= 10; i++) // { // util.SetCellValue(i, 1, "学号" + i.ToString()); // util.SetCellValue(i, 2, "姓名" + i.ToString()); // util.SetCellValue(i, 3, "性别" + i.ToString()); // util.SetCellValue(i, 4, "年龄" + i.ToString()); // } // util.SetRange(1, 1, 10, 4); // util.SetAliment(Excel.XlHAlign.xlHAlignCenter, Excel.XlVAlign.xlVAlignCenter); // //util.SetBorders(Excel.XlLineStyle.xlSlantDashDot); // util.SetLeftBorder(Excel.XlLineStyle.xlContinuous); // string exportPath = string.Format(@"{0}{1}", dir, "1.xlsx"); // util.SaveAs(exportPath); //} #endregion Console.Read(); }