//按Sheet合并所有文件
void AllCombineBySheet()
{
bool firstRowIsHead = true;
if (!checkBox_firstrowishead.Checked)
{
firstRowIsHead = false;
}
System.Windows.Forms.OpenFileDialog fileDialog = new System.Windows.Forms.OpenFileDialog();
fileDialog.Multiselect = true;
System.Windows.Forms.DialogResult result = fileDialog.ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
string basestr = string.Empty;
string targetfilename = string.Empty;
string[] filenames = fileDialog.FileNames;
basestr = filenames[0];
targetfilename = basestr.Substring(0, basestr.LastIndexOf("\\") + 1) + "AllInOneFileBySheet.xlsx";
int num = 0;
string targetsheet = "Sheet1";
for (int i = 0; i < filenames.Length; i++)
{
ExcelEdit ee = new ExcelEdit();
ee.Open(filenames[i]);
StringCollection sc = ee.ExcelSheetNames(filenames[i]);
for (int j = 0; j < sc.Count; j++)
{
targetsheet = sc[j].Substring(0, sc[j].Length - 1);
DataTable dt = ExcelUtil.ExcelToDataTable(sc[j].Substring(0, sc[j].Length - 1), firstRowIsHead, filenames[i], false);
if (num == 0)
{
string[] sheetnames = new string[sc.Count - 1];
for (int s = 1; s < sc.Count; s++)
{
sheetnames[s - 1] = sc[s].Substring(0, sc[s].Length - 1);
}
ExcelUtil.DataTableToExcel(dt, targetsheet, true, targetfilename, sheetnames);
}
else
{
//ExcelUtil.appendInfoToFile(targetfilename, targetsheet, dt);
ExcelUtil.DataTableToExcel(dt, targetfilename, targetsheet);
}
num++;
}
ee.Close();
}
}
}
//按Sheet合并所有文件-horizontal
void AllCombineBySheet_horizontal()
{
bool firstRowIsHead = true;
if (!checkBox_firstrowishead.Checked)
{
firstRowIsHead = false;
}
System.Windows.Forms.OpenFileDialog fileDialog = new System.Windows.Forms.OpenFileDialog();
fileDialog.Multiselect = true;
System.Windows.Forms.DialogResult result = fileDialog.ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
string basestr = string.Empty;
string targetfilename = string.Empty;
string[] filenames = fileDialog.FileNames;
basestr = filenames[0];
targetfilename = basestr.Substring(0, basestr.LastIndexOf("\\") + 1) + "AllInOneFileBySheet-horizontal.xlsx";
Dictionary <string, List <DataTable> > sheet_dtlist_dic = new Dictionary <string, List <DataTable> >();
for (int i = 0; i < filenames.Length; i++)
{
ExcelEdit ee = new ExcelEdit();
ee.Open(filenames[i]);
StringCollection sc = ee.ExcelSheetNames(filenames[i]);
ee.Close();
for (int j = 0; j < sc.Count; j++)
{
string targetsheet = sc[j].Substring(0, sc[j].Length - 1);
DataTable dt = ExcelUtil.ExcelToDataTable(sc[j].Substring(0, sc[j].Length - 1), firstRowIsHead, filenames[i], false);
if (!sheet_dtlist_dic.ContainsKey(targetsheet))
{
sheet_dtlist_dic.Add(targetsheet, new List <DataTable>());
}
sheet_dtlist_dic[targetsheet].Add(dt);
}
}
foreach (var key in sheet_dtlist_dic.Keys)
{
DataTable allDt = new DataTable();
List <DataTable> dtlist = sheet_dtlist_dic[key];
for (int j = 0; j < dtlist.Count; j++)
{
allDt = DataTableHelper.UniteDataTable(allDt, dtlist[j], "");
}
ExcelUtil.DataTableToExcel(allDt, targetfilename, key);
}
}
}
//按指定Sheet合并到新文件对应Sheet-horizontal
void CombineAppointSheet_horizontal()
{
string[] targetSheetNames = this.textBox_appointSheetName.Lines;
bool firstRowIsHead = true;
if (!checkBox_firstrowishead.Checked)
{
firstRowIsHead = false;
}
System.Windows.Forms.OpenFileDialog fileDialog = new System.Windows.Forms.OpenFileDialog();
fileDialog.Multiselect = true;
System.Windows.Forms.DialogResult result = fileDialog.ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
string[] filenames = fileDialog.FileNames;
string basestr = filenames[0];
string targetfilename = basestr.Substring(0, basestr.LastIndexOf("\\") + 1) + "AllAppoint-horizontal.xlsx";
Dictionary <string, List <DataTable> > sheet_dtlist_dic = new Dictionary <string, List <DataTable> >();
for (int i = 0; i < filenames.Length; i++)
{
for (int j = 0; j < targetSheetNames.Length; j++)
{
string targetsheet = targetSheetNames[j];
DataTable dt = ExcelUtil.ExcelToDataTable(targetsheet, firstRowIsHead, filenames[i], false);
if (!sheet_dtlist_dic.ContainsKey(targetsheet))
{
sheet_dtlist_dic.Add(targetsheet, new List <DataTable>());
}
sheet_dtlist_dic[targetsheet].Add(dt);
}
}
foreach (var key in sheet_dtlist_dic.Keys)
{
DataTable allDt = new DataTable();
List <DataTable> dtlist = sheet_dtlist_dic[key];
for (int j = 0; j < dtlist.Count; j++)
{
allDt = DataTableHelper.UniteDataTable(allDt, dtlist[j], "");
}
ExcelUtil.DataTableToExcel(allDt, targetfilename, key);
}
}
}
//所有Sheet合并成1个-horizontal
void AllInOneSheet_horizontal()
{
bool firstRowIsHead = true;
if (!checkBox_firstrowishead.Checked)
{
firstRowIsHead = false;
}
System.Windows.Forms.OpenFileDialog fileDialog = new System.Windows.Forms.OpenFileDialog();
fileDialog.Multiselect = true;
System.Windows.Forms.DialogResult result = fileDialog.ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
string basestr = string.Empty;
string targetfilename = string.Empty;
string[] filenames = fileDialog.FileNames;
basestr = filenames[0];
targetfilename = basestr.Substring(0, basestr.LastIndexOf("\\") + 1) + "AllInOneSheet-horizontal.xlsx";
string targetsheet = "Sheet1";
DataTable allDt = new DataTable();
for (int i = 0; i < filenames.Length; i++)
{
ExcelEdit ee = new ExcelEdit();
ee.Open(filenames[i]);
StringCollection sc = ee.ExcelSheetNames(filenames[i]);
ee.Close();
for (int j = 0; j < sc.Count; j++)
{
DataTable dt = ExcelUtil.ExcelToDataTable(sc[j].Substring(0, sc[j].Length - 1), firstRowIsHead, filenames[i], false);
//合并datatable
allDt = DataTableHelper.UniteDataTable(allDt, dt, "");
}
}
ExcelUtil.DataTableToExcel(allDt, targetsheet, true, targetfilename, null);
}
}