1.下面两个文件.xls是给的文件，实际上是.xml文件
2.具体的代码
private void btOK_Click(object sender, EventArgs e)
{
//0.获取路径文件夹
this.btOK.Enabled = false;
this.textBox1.Text = System.Windows.Forms.Application.StartupPath + "\\de";
strAllFiles = Directory.GetFiles(System.Windows.Forms.Application.StartupPath + "\\de","*.xls"); showMessage("正在执行修改错误文件格式......");
System.Windows.Forms.Application.DoEvents();
Thread.Sleep(); //1.修改文件扩展名
EditFileName();
//2.保存新的扩展名
CheckExeclEditing();
//3.读取数据并合并数据
#region
//strAllFiles = Directory.GetFiles(this.textBox1.Text);
strName = new string[strAllFiles.Length * ];
DataSet[] ds = new DataSet[strAllFiles.Length * ];
int j = ;
for (int i = ; i < strAllFiles.Length; i++)
{
string sql = null;
System.Data.DataTable TableName = ExcelAPI.LoadDataFromExcel(strAllFiles[i]);
if (TableName.Rows.Count > )
{
foreach (DataRow item in TableName.Rows)
{
if (!item["TABLE_NAME"].ToString().Contains("Print_Titles"))
{
sql = string.Format("SELECT * FROM [{0}] WHERE F3 is not null and F3 not like '单位'", item["TABLE_NAME"].ToString());
ds[j] = (ExcelAPI.LoadDataFromExcel(strAllFiles[i], sql));
strName[j] = System.IO.Path.GetFileNameWithoutExtension(strAllFiles[i]);
j++;
}
}
}
showMessage("正在执行" + System.IO.Path.GetFileNameWithoutExtension(strAllFiles[i]) + "文件......");
System.Windows.Forms.Application.DoEvents();
Thread.Sleep();
} string[] st = new string[];
for (int i = ; i < st.Length; i++)
{
st[i] = i.ToString();
}
string path = textBox1.Text.Substring(textBox1.Text.LastIndexOf("\\") + ) + "_" + DateTime.Now.ToString("yyyy年MM月dd日hh点mm分ss秒") + ".xls";
showMessage("正在执行合并文件......");
System.Windows.Forms.Application.DoEvents();
Thread.Sleep();
bool result = ExcelAPI.WebExportToExcel_1(ds, textBox1.Text, path, strName, , true);
if (result == true)
{
MessageBox.Show("成功");
this.btOK.Enabled = true;
System.Windows.Forms.Application.ExitThread();
}
else
{
this.btOK.Enabled = true;
MessageBox.Show("失败");
}
#endregion
}
private void EditFileName()
{
try
{ strAllFiles = Directory.GetFiles(this.textBox1.Text,"*.xls");
for (int i = ; i < strAllFiles.Length; i++)
{
byte[] bT = File.ReadAllBytes(strAllFiles[i]);
FileStream fs = File.Create(strAllFiles[i].Replace(".xls", ".xml"));
fs.Write(bT, , bT.Length);
fs.Close();
File.Delete(strAllFiles[i]);
}
}
catch (Exception ex)
{
Log.WriteFileError(ex);
}
} private void CheckExeclEditing()
{
try
{
for (int i = ; i < strAllFiles.Length; i++)
{
string strFileName = strAllFiles[i].Replace(".xls", ".xml");
string str = strFileName.Replace(".xml", ".xls");
Microsoft.Office.Interop.Excel._Application execl = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook bookDes1t = (Microsoft.Office.Interop.Excel.Workbook)execl.Workbooks.Open(strFileName);
bookDes1t.SaveAs(str, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
bookDes1t.Close();
execl.Application.Quit();
}
}
catch (Exception ex)
{
Log.WriteFileError(ex);
} }
public class ExcelAPI
{
/// <summary>
/// 获取表名称
/// </summary>
/// <param name="filePath">路径</param>
/// <returns></returns>
public static System.Data.DataTable LoadDataFromExcel(string filePath)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";//只能打开2003
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
//string sql=string.Format("SELECT * FROM [{0}$]", strSheetName);//可更改Sheet名称，比如sheet2，等等
System.Data.DataTable DataNames = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
OleConn.Close();
return DataNames; }
catch (Exception err)
{
Log.WriteFileError(err);
//MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
// MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
//加载Excel
public static DataSet LoadDataFromExcel(string filePath, string sqlCmd)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";//只能打开2003
//strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", filePath);//可打开2007 using (OleDbConnection OleConn = new OleDbConnection(strConn))
{
//string sql =string.Format("SELECT * FROM [{0}$]", strSheetName);//可更改Sheet名称，比如sheet2，等等 using (OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sqlCmd, OleConn))
{
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, sqlCmd);
//MessageBox.Show(OleDsExcle.Tables[strSheetName].Rows[3][1].ToString());
return OleDsExcle;
}
}
}
catch (Exception err)
{
Log.WriteFileError(err);
//MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
// MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
} /// <param name="dv">用于导出的DataSET[数组]</param>
/// <param name="tmpExpDir">导出的文件夹路径，例如d:/</param>
/// <param name="refFileName">文件名，例如test.xls</param>
/// <param name="sheetName">Sheet的名称，如果导出多个Sheet[数租]</param>
/// <param name="sheetSize">每个Sheet包含的数据行数，此数值不包括标题行。所以，对于65536行数据，请将此值设置为65535</param>
/// <param name="setBorderLine">导出完成后，是否给数据加上边框线</param>
public static bool WebExportToExcel_1(DataSet[] dv, string tmpExpDir, string refFileName, string[] strName, int sheetSize, bool setBorderLine)
{
try
{ string[] str = { "定额编号", "编号", "人材机名称", "人材机单位", "数量", "人材机单价" };
int RowsToDivideSheet = sheetSize;//计算Sheet行数
int sheetCount = dv.Length;
GC.Collect();// 回收其他的垃圾
Microsoft.Office.Interop.Excel.Application excel; _Workbook xBk; _Worksheet xSt = null;
excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true);
int dvRowEnd; int rowIndex = ; int colIndex = ;
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, , Type.Missing);
xSt.Name = "数据信息合并";
foreach (string item in str)
{
//设置标题格式
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
//设置标题居中对齐
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
//填值，并进行下一列
excel.Cells[rowIndex, colIndex++] = item;
}
//对全部Sheet进行操作
for (int sheetIndex = ; sheetIndex < sheetCount; sheetIndex++)
{
Log.WriteFile(strName[sheetIndex]);
//计算结束行
dvRowEnd = RowsToDivideSheet;
if (dvRowEnd > dv[sheetIndex].Tables[].Rows.Count)
{ dvRowEnd = dv[sheetIndex].Tables[].Rows.Count + ; } int i = ;
//以下代码就是经过修正后的。上面注释的代码有问题。
foreach (DataRow dr in dv[sheetIndex].Tables[].Rows)
{
//新起一行，当前单元格移至行首
rowIndex++;
colIndex = ;
excel.Cells[rowIndex, colIndex] = strName[sheetIndex];
excel.Cells[rowIndex, ++colIndex] = dr[].ToString();
excel.Cells[rowIndex, ++colIndex] = dr[].ToString();
excel.Cells[rowIndex, ++colIndex] = dr[].ToString();
excel.Cells[rowIndex, ++colIndex] = dr[].ToString();
excel.Cells[rowIndex, ++colIndex] = dr[].ToString(); }
Range allDataWithTitleRange = xSt.get_Range(excel.Cells[, ], excel.Cells[rowIndex, colIndex]);
allDataWithTitleRange.Select();
allDataWithTitleRange.Columns.AutoFit();
if (setBorderLine)
{
allDataWithTitleRange.Borders.LineStyle = ;
} }//Sheet循环结束
string absFileName = System.IO.Path.Combine(tmpExpDir, refFileName);
xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null; excel = null; xSt = null; GC.Collect();
return true;
}
catch (Exception ex)
{
Log.WriteFileError(ex);
//MessageBox.Show("导入Excel出错！错误原因：" + ex.Message, "提示信息",
// MessageBoxButtons.OK, MessageBoxIcon.Information);
return false; }
}
}
3.日志文件
public class Log
{
public static void WriteFileError(Exception ex)
{
String sFileName;
String sFilePath = Path.Combine(Application.StartupPath, @"Log\错误日志文件");
if (Directory.Exists(sFilePath) == false)
Directory.CreateDirectory(sFilePath);
else
{
DirectoryInfo dInfo = new DirectoryInfo(sFilePath);
if (dInfo.GetFiles().Length > )
foreach (FileInfo fInfo in dInfo.GetFiles())
fInfo.Delete();
}
//用当前日期（年月日）作为文件名
sFileName = DateTime.Now.ToShortDateString().Replace("/", "-") + ".log"; //文件名不能包括:
sFilePath = Path.Combine(sFilePath, sFileName); StreamWriter streamWriter; if (File.Exists(sFilePath))
streamWriter = File.AppendText(sFilePath);
else
streamWriter = File.CreateText(sFilePath); streamWriter.WriteLine();
streamWriter.WriteLine(DateTime.Now.ToString());
streamWriter.WriteLine(ex.ToString());
streamWriter.WriteLine(ex.Message);
streamWriter.WriteLine(ex.InnerException);
if (ex is DetailException)
{
streamWriter.Write(((DetailException)ex).additionalMsg);
streamWriter.WriteLine();
}
streamWriter.Close();
}
public static void WriteFile(string exFile)
{
String sFileName;
String sFilePath = Path.Combine(Application.StartupPath, @"Log\操作文件日志");
if (Directory.Exists(sFilePath) == false)
Directory.CreateDirectory(sFilePath);
else
{
DirectoryInfo dInfo = new DirectoryInfo(sFilePath);
if (dInfo.GetFiles().Length > )
foreach (FileInfo fInfo in dInfo.GetFiles())
fInfo.Delete();
}
//用当前日期（年月日）作为文件名
sFileName = DateTime.Now.ToShortDateString().Replace("/", "-") + ".log"; //文件名不能包括:
sFilePath = Path.Combine(sFilePath, sFileName); StreamWriter streamWriter; if (File.Exists(sFilePath))
streamWriter = File.AppendText(sFilePath);
else
streamWriter = File.CreateText(sFilePath); streamWriter.WriteLine();
streamWriter.WriteLine(DateTime.Now.ToString());
streamWriter.WriteLine(exFile);
streamWriter.Close();
}
}
public class DetailException : Exception
{
public Exception exception;
public string additionalMsg; public DetailException(Exception ex, string additionalMsg)
{
exception = ex;
this.additionalMsg = additionalMsg;
}
} public class ExceptionHandler
{
public static StringBuilder strLog = new StringBuilder(); public static void handlingExcetion(Exception ex)
{
if (ex == null) return; strLog.Append(DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString() + "||" + ex.Message);
strLog.Append("------------------" + ex.StackTrace + "\r\n\r\n"); Exception finalEx = ex; while (ex.InnerException != null && !ex.InnerException.Equals(finalEx))
{
finalEx = ex.InnerException;
}
try
{
Log.WriteFileError(finalEx);
}
catch (Exception e)
{
System.Diagnostics.Trace.Write(e.Message);
}
}
}