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;
}
