Winform合并多个Excel文件到一个文件中（源文件.xls,实际是.xml）
1.下面两个文件.xls是给的文件，实际上是.xml文件
2.具体的代码
1 private void btOK_Click(object sender, EventArgs e) 2 { 3 //0.获取路径文件夹 4 this.btOK.Enabled = false; 5 this.textBox1.Text = System.Windows.Forms.Application.StartupPath + "\\de"; 6 strAllFiles = Directory.GetFiles(System.Windows.Forms.Application.StartupPath + "\\de","*.xls"); 7 8 showMessage("正在执行修改错误文件格式......"); 9 System.Windows.Forms.Application.DoEvents(); 10 Thread.Sleep(500); 11 12 //1.修改文件扩展名 13 EditFileName(); 14 //2.保存新的扩展名 15 CheckExeclEditing(); 16 //3.读取数据并合并数据 17 #region 18 //strAllFiles = Directory.GetFiles(this.textBox1.Text); 19 strName = new string[strAllFiles.Length * 2]; 20 DataSet[] ds = new DataSet[strAllFiles.Length * 2]; 21 int j = 0; 22 for (int i = 0; i < strAllFiles.Length; i++) 23 { 24 string sql = null; 25 System.Data.DataTable TableName = ExcelAPI.LoadDataFromExcel(strAllFiles[i]); 26 if (TableName.Rows.Count > 0) 27 { 28 foreach (DataRow item in TableName.Rows) 29 { 30 if (!item["TABLE_NAME"].ToString().Contains("Print_Titles")) 31 { 32 sql = string.Format("SELECT * FROM [{0}] WHERE F3 is not null and F3 not like '单位'", item["TABLE_NAME"].ToString()); 33 ds[j] = (ExcelAPI.LoadDataFromExcel(strAllFiles[i], sql)); 34 strName[j] = System.IO.Path.GetFileNameWithoutExtension(strAllFiles[i]); 35 j++; 36 } 37 } 38 } 39 showMessage("正在执行" + System.IO.Path.GetFileNameWithoutExtension(strAllFiles[i]) + "文件......"); 40 System.Windows.Forms.Application.DoEvents(); 41 Thread.Sleep(1000); 42 } 43 44 string[] st = new string[6]; 45 for (int i = 0; i < st.Length; i++) 46 { 47 st[i] = i.ToString(); 48 } 49 string path = textBox1.Text.Substring(textBox1.Text.LastIndexOf("\\") + 1) + "_" + DateTime.Now.ToString("yyyy年MM月dd日hh点mm分ss秒") + ".xls"; 50 showMessage("正在执行合并文件......"); 51 System.Windows.Forms.Application.DoEvents(); 52 Thread.Sleep(1000); 53 bool result = ExcelAPI.WebExportToExcel_1(ds, textBox1.Text, path, strName, 65535, true); 54 if (result == true) 55 { 56 MessageBox.Show("成功"); 57 this.btOK.Enabled = true; 58 System.Windows.Forms.Application.ExitThread(); 59 } 60 else 61 { 62 this.btOK.Enabled = true; 63 MessageBox.Show("失败"); 64 } 65 #endregion 66 }
1 private void EditFileName() 2 { 3 try 4 { 5 6 strAllFiles = Directory.GetFiles(this.textBox1.Text,"*.xls"); 7 for (int i = 0; i < strAllFiles.Length; i++) 8 { 9 byte[] bT = File.ReadAllBytes(strAllFiles[i]); 10 FileStream fs = File.Create(strAllFiles[i].Replace(".xls", ".xml")); 11 fs.Write(bT, 0, bT.Length); 12 fs.Close(); 13 File.Delete(strAllFiles[i]); 14 } 15 } 16 catch (Exception ex) 17 { 18 Log.WriteFileError(ex); 19 } 20 } 21 22 23 private void CheckExeclEditing() 24 { 25 try 26 { 27 for (int i = 0; i < strAllFiles.Length; i++) 28 { 29 string strFileName = strAllFiles[i].Replace(".xls", ".xml"); 30 string str = strFileName.Replace(".xml", ".xls"); 31 Microsoft.Office.Interop.Excel._Application execl = new Microsoft.Office.Interop.Excel.ApplicationClass(); 32 Microsoft.Office.Interop.Excel.Workbook bookDes1t = (Microsoft.Office.Interop.Excel.Workbook)execl.Workbooks.Open(strFileName); 33 bookDes1t.SaveAs(str, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); 34 bookDes1t.Close(); 35 execl.Application.Quit(); 36 } 37 } 38 catch (Exception ex) 39 { 40 Log.WriteFileError(ex); 41 } 42 43 }
1 public class ExcelAPI 2 { 3 /// <summary> 4 /// 获取表名称 5 /// </summary> 6 /// <param name="filePath">路径</param> 7 /// <returns></returns> 8 public static System.Data.DataTable LoadDataFromExcel(string filePath) 9 { 10 try 11 { 12 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";//只能打开2003 13 OleDbConnection OleConn = new OleDbConnection(strConn); 14 OleConn.Open(); 15 //string sql=string.Format("SELECT * FROM [{0}$]", strSheetName);//可更改Sheet名称，比如sheet2，等等 16 System.Data.DataTable DataNames = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 17 OleConn.Close(); 18 return DataNames; 19 20 } 21 catch (Exception err) 22 { 23 Log.WriteFileError(err); 24 //MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息", 25 // MessageBoxButtons.OK, MessageBoxIcon.Information); 26 return null; 27 } 28 } 29 //加载Excel 30 public static DataSet LoadDataFromExcel(string filePath, string sqlCmd) 31 { 32 try 33 { 34 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";//只能打开2003 35 //strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", filePath);//可打开2007 36 37 using (OleDbConnection OleConn = new OleDbConnection(strConn)) 38 { 39 //string sql =string.Format("SELECT * FROM [{0}$]", strSheetName);//可更改Sheet名称，比如sheet2，等等 40 41 using (OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sqlCmd, OleConn)) 42 { 43 DataSet OleDsExcle = new DataSet(); 44 OleDaExcel.Fill(OleDsExcle, sqlCmd); 45 //MessageBox.Show(OleDsExcle.Tables[strSheetName].Rows[3][1].ToString()); 46 return OleDsExcle; 47 } 48 } 49 } 50 catch (Exception err) 51 { 52 Log.WriteFileError(err); 53 //MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息", 54 // MessageBoxButtons.OK, MessageBoxIcon.Information); 55 return null; 56 } 57 } 58 59 /// <param name="dv">用于导出的DataSET[数组]</param> 60 /// <param name="tmpExpDir">导出的文件夹路径，例如d:/</param> 61 /// <param name="refFileName">文件名，例如test.xls</param> 62 /// <param name="sheetName">Sheet的名称，如果导出多个Sheet[数租]</param> 63 /// <param name="sheetSize">每个Sheet包含的数据行数，此数值不包括标题行。所以，对于65536行数据，请将此值设置为65535</param> 64 /// <param name="setBorderLine">导出完成后，是否给数据加上边框线</param> 65 public static bool WebExportToExcel_1(DataSet[] dv, string tmpExpDir, string refFileName, string[] strName, int sheetSize, bool setBorderLine) 66 { 67 try 68 { 69 70 string[] str = { "定额编号", "编号", "人材机名称", "人材机单位", "数量", "人材机单价" }; 71 int RowsToDivideSheet = sheetSize;//计算Sheet行数 72 int sheetCount = dv.Length; 73 GC.Collect();// 回收其他的垃圾 74 Microsoft.Office.Interop.Excel.Application excel; _Workbook xBk; _Worksheet xSt = null; 75 excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); 76 int dvRowEnd; int rowIndex = 1; int colIndex = 1; 77 xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); 78 xSt.Name = "数据信息合并"; 79 foreach (string item in str) 80 { 81 //设置标题格式 82 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; 83 //设置标题居中对齐 84 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; 85 //填值，并进行下一列 86 excel.Cells[rowIndex, colIndex++] = item; 87 } 88 //对全部Sheet进行操作 89 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) 90 { 91 Log.WriteFile(strName[sheetIndex]); 92 //计算结束行 93 dvRowEnd = RowsToDivideSheet; 94 if (dvRowEnd > dv[sheetIndex].Tables[0].Rows.Count) 95 { dvRowEnd = dv[sheetIndex].Tables[0].Rows.Count + 1; } 96 97 int i = 0; 98 //以下代码就是经过修正后的。上面注释的代码有问题。 99 foreach (DataRow dr in dv[sheetIndex].Tables[0].Rows) 100 { 101 //新起一行，当前单元格移至行首 102 rowIndex++; 103 colIndex = 1; 104 excel.Cells[rowIndex, colIndex] = strName[sheetIndex]; 105 excel.Cells[rowIndex, ++colIndex] = dr[0].ToString(); 106 excel.Cells[rowIndex, ++colIndex] = dr[1].ToString(); 107 excel.Cells[rowIndex, ++colIndex] = dr[2].ToString(); 108 excel.Cells[rowIndex, ++colIndex] = dr[3].ToString(); 109 excel.Cells[rowIndex, ++colIndex] = dr[4].ToString(); 110 111 } 112 Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex]); 113 allDataWithTitleRange.Select(); 114 allDataWithTitleRange.Columns.AutoFit(); 115 if (setBorderLine) 116 { 117 allDataWithTitleRange.Borders.LineStyle = 1; 118 } 119 120 121 }//Sheet循环结束 122 string absFileName = System.IO.Path.Combine(tmpExpDir, refFileName); 123 xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); 124 excel.Quit(); 125 System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); 126 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); 127 System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); 128 xBk = null; excel = null; xSt = null; GC.Collect(); 129 return true; 130 } 131 catch (Exception ex) 132 { 133 Log.WriteFileError(ex); 134 //MessageBox.Show("导入Excel出错！错误原因：" + ex.Message, "提示信息", 135 // MessageBoxButtons.OK, MessageBoxIcon.Information); 136 return false; 137 138 } 139 } 140 }
3.日志文件
1 public class Log 2 { 3 public static void WriteFileError(Exception ex) 4 { 5 String sFileName; 6 String sFilePath = Path.Combine(Application.StartupPath, @"Log\错误日志文件"); 7 if (Directory.Exists(sFilePath) == false) 8 Directory.CreateDirectory(sFilePath); 9 else 10 { 11 DirectoryInfo dInfo = new DirectoryInfo(sFilePath); 12 if (dInfo.GetFiles().Length > 100) 13 foreach (FileInfo fInfo in dInfo.GetFiles()) 14 fInfo.Delete(); 15 } 16 //用当前日期（年月日）作为文件名 17 sFileName = DateTime.Now.ToShortDateString().Replace("/", "-") + ".log"; //文件名不能包括: 18 sFilePath = Path.Combine(sFilePath, sFileName); 19 20 StreamWriter streamWriter; 21 22 if (File.Exists(sFilePath)) 23 streamWriter = File.AppendText(sFilePath); 24 else 25 streamWriter = File.CreateText(sFilePath); 26 27 streamWriter.WriteLine(); 28 streamWriter.WriteLine(DateTime.Now.ToString()); 29 streamWriter.WriteLine(ex.ToString()); 30 streamWriter.WriteLine(ex.Message); 31 streamWriter.WriteLine(ex.InnerException); 32 if (ex is DetailException) 33 { 34 streamWriter.Write(((DetailException)ex).additionalMsg); 35 streamWriter.WriteLine(); 36 } 37 streamWriter.Close(); 38 } 39 public static void WriteFile(string exFile) 40 { 41 String sFileName; 42 String sFilePath = Path.Combine(Application.StartupPath, @"Log\操作文件日志"); 43 if (Directory.Exists(sFilePath) == false) 44 Directory.CreateDirectory(sFilePath); 45 else 46 { 47 DirectoryInfo dInfo = new DirectoryInfo(sFilePath); 48 if (dInfo.GetFiles().Length > 100) 49 foreach (FileInfo fInfo in dInfo.GetFiles()) 50 fInfo.Delete(); 51 } 52 //用当前日期（年月日）作为文件名 53 sFileName = DateTime.Now.ToShortDateString().Replace("/", "-") + ".log"; //文件名不能包括: 54 sFilePath = Path.Combine(sFilePath, sFileName); 55 56 57 StreamWriter streamWriter; 58 59 if (File.Exists(sFilePath)) 60 streamWriter = File.AppendText(sFilePath); 61 else 62 streamWriter = File.CreateText(sFilePath); 63 64 streamWriter.WriteLine(); 65 streamWriter.WriteLine(DateTime.Now.ToString()); 66 streamWriter.WriteLine(exFile); 67 streamWriter.Close(); 68 } 69 }
1 public class DetailException : Exception 2 { 3 public Exception exception; 4 public string additionalMsg; 5 6 public DetailException(Exception ex, string additionalMsg) 7 { 8 exception = ex; 9 this.additionalMsg = additionalMsg; 10 } 11 } 12 13 public class ExceptionHandler 14 { 15 public static StringBuilder strLog = new StringBuilder(); 16 17 public static void handlingExcetion(Exception ex) 18 { 19 if (ex == null) return; 20 21 strLog.Append(DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString() + "||" + ex.Message); 22 strLog.Append("------------------" + ex.StackTrace + "\r\n\r\n"); 23 24 Exception finalEx = ex; 25 26 while (ex.InnerException != null && !ex.InnerException.Equals(finalEx)) 27 { 28 finalEx = ex.InnerException; 29 } 30 try 31 { 32 Log.WriteFileError(finalEx); 33 } 34 catch (Exception e) 35 { 36 System.Diagnostics.Trace.Write(e.Message); 37 } 38 } 39 }
