Beispiel #1
0
        //按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();
                }
            }
        }
Beispiel #2
0
        //按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);
                }
            }
        }
Beispiel #3
0
        //所有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);
            }
        }