private void CreateExcelTree() { List <ExcelDataHelper> Excels = new List <ExcelDataHelper>(); foreach (TreeNode rnode in treeView1.Nodes[0].Nodes) { string fname = rnode.Tag as string; TreeNode node = treeView1.Nodes[0].Nodes[Path.GetFileName(fname)]; ExcelDataHelper edh = new ExcelDataHelper(fname); Excels.Add(edh); string[] sheetnames = edh.GetSheetNames(); foreach (string sheet in sheetnames) { TreeNode cnode = new TreeNode(); cnode.Text = sheet; cnode.Name = sheet; node.Nodes.Add(cnode); GZDataTable gzdt = GZDataTable.ImportFromExcel(fname, sheet); string[] cols = gzdt.GetColumnsName(); foreach (string col in cols) { cnode.Nodes.Add(col); } } } }
public static GZDataTable ImportFromExcel(string filepath) { ExcelDataHelper excel = new ExcelDataHelper(filepath); DataSet ds = excel.ToDataSet(true, 2); GZDataTable gzdt = new GZDataTable(ds.DataSetName, ds.Tables[0].TableName, ds.Tables[0]); return(gzdt); //return NPOIHelper.Import(filepath); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static GZDataTable Import(string strFileName) { string TableTitle; string DateString; DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow titleRow = sheet.GetRow(sheet.FirstRowNum); TableTitle = titleRow.Cells[0].ToString().ToString().Trim(); TableTitle = TableTitle.Replace("\0", ""); IRow DateRow = sheet.GetRow(sheet.FirstRowNum + 1); DateString = DateRow.Cells[0].ToString().ToString().Trim().Replace("\0", ""); IRow headerRow = sheet.GetRow(IndexOfTitleRow); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + IndexOfTitleRow + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); bool flag = true; for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); if (dataRow[j].ToString().IndexOf("合计") >= 0) { flag = false; } } } if (flag) { dt.Rows.Add(dataRow); } } GZDataTable result = new GZDataTable(DateString, TableTitle, dt); return(result); }
public void ImportFromExcel(string[] filepaths, string[] RejectNames) { YearTables = new Dictionary <string, GZDataTable>(); foreach (string file in filepaths) { GZDataTable gzdt = GZDataTable.ImportFromExcel(file); gzdt.DeleteBlankOrZeroColumns(RejectNames); YearTables.Add(gzdt.DateStr, gzdt); } }
public static GZDataTable ImportFromExcel(string filepath, string sheetname) { ExcelDataHelper excel = new ExcelDataHelper(filepath); DataTable dt = excel.ToDataTable(sheetname, true, 2); GZDataTable gzdt = new GZDataTable(Path.GetFileName(filepath), sheetname, dt); return(gzdt); //return NPOIHelper.Import(filepath); }
private void button2_Click(object sender, EventArgs e) { foreach (var item in GetCheckedFiles()) { GZDataTable gzdt = GZDataTable.ImportFromExcel(item.ToString()); gzdt.DeleteBlankOrZeroColumns(RejectNames); this.dataGridView1.AutoGenerateColumns = true; this.dataGridView1.DataSource = gzdt; this.dataGridView1.AutoResizeRows(); toolStripStatusLabel1.Text = gzdt.Rows.Count.ToString(); gzdt.ExportToExcel(); } }
public void Summary(string key_column, string[] reserve_columns, string[] expend_columns) { List <string> months = new List <string>(YearTables.Keys); months.Sort(); HashSet <string> keyset = new HashSet <string>(); foreach (string month in months) { GZDataTable gzdt = YearTables[month]; foreach (DataRow row in gzdt.Rows) { keyset.Add(row[key_column].ToString()); } } List <string> Keys = new List <string>(keyset); DataTable result = new DataTable(); result.Columns.Add(key_column); foreach (var col in reserve_columns) { result.Columns.Add(col); } foreach (string month in months) { //result.Columns.Add(expandcol); foreach (var expandcol in expend_columns) { result.Columns.Add(month + expandcol); } } months.Sort(); Keys.Sort(); //填充数据 foreach (string key in Keys) { DataRow row = result.NewRow(); row[key_column] = key; foreach (string month in months) { GZDataTable gzdt = YearTables[month]; foreach (DataRow mrow in gzdt.Rows) { if (mrow[key_column].ToString() == key) { foreach (var col in reserve_columns) { row[col] = mrow[col]; } foreach (string expandcol in expend_columns) { row[month + expandcol] = mrow[expandcol]; } break; } } } result.Rows.Add(row); } SummaryTable = result; }