ExcelHelper

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;

namespace 数据检错工具
{

public class ExcelHelper
{

Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
//保存目标的对象
Excel.Workbook bookDest = null;
Excel.Worksheet sheetDest = null;
//读取数据的对象
Excel.Workbook bookSource = null;
Excel.Worksheet sheetSource = null;
string[] _sourceFiles = null;
string _destFile = string.Empty;
string _columnEnd = string.Empty;
int _worksheetIndex = 0;//要合并工作簿中的第几个工作表，索引值从1开始
int _headerRowCount = 1;
int _currentRowCount = 0;

public ExcelHelper()
{
Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();

}
public ExcelHelper(string[] sourceFiles, string destFile, int worksheetIndex, string columnEnd, int headerRowCount)
{

bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
sheetDest.Name = "Data";
_sourceFiles = sourceFiles;
_destFile = destFile;
_columnEnd = columnEnd;
_worksheetIndex = worksheetIndex;
_headerRowCount = headerRowCount;
}
/// <summary>
/// 打开工作表
/// </summary>
/// <param name="fileName"></param>
bool OpenBook(string fileName, ref string pErrInfo)
{
bool pFlagOK = true;
try
{
if (System.IO.File.Exists(fileName) == false)
{
pFlagOK = false;
pErrInfo = "要打开的Excel文件不存在:" + fileName;
}
else
{
bookSource = app.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
sheetSource = bookSource.Worksheets[_worksheetIndex] as Excel.Worksheet;
}
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "打开Excel文件异常:" + e.Message;
}

return pFlagOK;

}
/// <summary>
/// 关闭工作表
/// </summary>
void CloseBook()
{
try
{
bookSource.Close(false, Missing.Value, Missing.Value);
}
catch (Exception)
{

}

}
/// <summary>
/// 复制表头
/// </summary>
bool CopyHeader(ref string pErrInfo)
{
bool pFlagOK = true;
try
{
Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
range.Copy(sheetDest.get_Range("A1", Missing.Value));
_currentRowCount += _headerRowCount;
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "复制表头异常:" + e.Message;
}

return pFlagOK;
}
/// <summary>
/// 复制数据
/// </summary>
bool CopyData(ref string pErrInfo)
{
bool pFlagOK = true;
try
{
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount + 1), _columnEnd + sheetRowCount.ToString());
range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount + 1), Missing.Value));
_currentRowCount += range.Rows.Count;

//设置所有列宽按内容长度自适应
Excel.Range rangeTemp = sheetDest.get_Range("A1", _columnEnd + _currentRowCount.ToString());
rangeTemp.EntireColumn.AutoFit();
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "复制数据异常:" + e.Message;
}

return pFlagOK;

}
/// <summary>
/// 保存结果
/// </summary>
bool Save(ref string pErrInfo)
{
bool pFlagOK = true;
try
{
bookDest.Saved = true;
bookDest.SaveCopyAs(_destFile);
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "合并结果保存异常:" + e.Message;
}

return pFlagOK;

}
/// <summary>
/// 退出进程
/// </summary>
void Quit()
{
try
{
app.Quit();
}
catch (Exception)
{

}

}
/// <summary>
/// 合并
/// </summary>
bool DoMerge(ref string pErrInfo)
{
bool pFlagOK = true;
try
{
bool b = false;
foreach (string strFile in _sourceFiles)
{
if (OpenBook(strFile, ref pErrInfo) == false)
{
pFlagOK = false;
break;
}
else
{
if (b == false)
{
if (CopyHeader(ref pErrInfo) == false)
{
pFlagOK = false;
break;
}
else
{
b = true;
}
}

if (CopyData(ref pErrInfo) == false)
{
pFlagOK = false;
CloseBook();
break;
}
else
{
CloseBook();
}
}
}

if (pFlagOK)
{
if (Save(ref pErrInfo) == false)
{
pFlagOK = false;
}
}
Quit();
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "合并Excel异常:" + e.Message;
}

return pFlagOK;
}
/// <summary>
/// 合并表格
/// </summary>
/// <param name="sourceFiles">源文件</param>
/// <param name="destFile">目标文件</param>
/// <param name="columnEnd">最后一列标志</param>
/// <param name="headerRowCount">表头行数</param>
public static bool DoMerge(string[] sourceFiles, string destFile, int worksheetIndex, string columnEnd, int headerRowCount, ref string pErrInfo)
{
bool pFlagOK = true;
try
{
ExcelHelper mg = new ExcelHelper(sourceFiles, destFile, worksheetIndex, columnEnd, headerRowCount);
if (mg.DoMerge(ref pErrInfo) == false)
{
pFlagOK = false;
}
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "合并Excel出现错误:" + e.Message;
}

return pFlagOK;

}

/// <summary>
/// 根据excle的路径把第一个sheel中的内容放入datatable
/// </summary>
/// <param name="path">excel文件路径</param>
/// <param name="sheet">要读取的工作表名</param>
/// <param name="pDataTable">获取的数据表对象</param>
/// <param name="pErrInfo">读取失败的错误信息</param>
/// <returns></returns>
public static bool ReadExcel(string path, string sheet, ref DataTable pDataTable, ref string pErrInfo)
{

bool pFlagOK = true;
string realSheet = "";

try
{

//连接字符串
// Office 07及以上版本 不能出现多余的空格 而且分号注意
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR=YES表示有列标题行
//string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //Office 07以下版本
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
bool finded = false;
foreach (DataRow dr in sheetsName.Rows)
{
if (dr.Field<string>(2).IndexOf(sheet) >= 0)
{
realSheet = dr.Field<string>(2);
finded = true;
break;
}
}
if (finded == false)
{
pFlagOK = false;
pErrInfo = "Excel文件(" + path + ")中没有找到工作表: " + sheet;
}
string sql = string.Format("SELECT * FROM [{0}]", realSheet); //查询字符串
//string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串

OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set, sheet);
pDataTable = set.Tables[0];
}
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "读取Excel出现错误:" + e.Message;
}
return pFlagOK;
}

/// <summary>
/// 根据excle的路径把制定多个工作表中的内容放入dataset
/// </summary>
/// <param name="path">excel文件路径</param>
/// <param name="sheets">要读取的工作表名数组</param>
/// <param name="pDataSet">获取的数据集对象</param>
/// <param name="pErrInfo">读取失败的错误信息</param>
/// <returns></returns>
public static bool ReadExcel(string path, string[] sheets, ref DataSet pDataSet, ref string pErrInfo)
{
bool pFlagOK = true;
string[] realSheets = new string[sheets.Length];

try
{

//连接字符串
// Office 07及以上版本 不能出现多余的空格 而且分号注意
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR=YES表示有列标题行
//string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //Office 07以下版本
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//遍历所有工作表名，取得表明中含有指定工作表名的准确名称
for (int i = 0; i < sheets.Length; i++)
{
bool finded = false;
foreach (DataRow dr in sheetsName.Rows)
{
if (dr.Field<string>(2).IndexOf(sheets[i]) >= 0)
{
realSheets[i] = dr.Field<string>(2);
finded = true;
break;
}
}
if (finded == false)
{
pFlagOK = false;
pErrInfo = "Excel文件(" + path + ")中没有找到工作表: " + sheets[i];
break;
}
}

for (int i = 0; i < realSheets.Length; i++)
{
//查询字符串
string sql = string.Format("SELECT * FROM [{0}]", realSheets[i]);
//string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串

OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
ada.Fill(pDataSet, sheets[i]);
}

}
}
catch (Exception e)
{
pFlagOK = false;
pErrInfo = "读取Excel出现错误:" + e.Message;
}
return pFlagOK;
}

}

}