public class ExcelOper
{
private Application _excelApp = null;
public ExcelOper()
{
}
#region 读取Excel的内容
/// <summary>
/// 获取Excel的内容
/// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable
/// 的列标题
/// </summary>
/// <returns></returns>
public static System.Data.DataTable GetExcelTable(string excelPath, string sheetName)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = OpenExcelEx(excelPath, out connString);
//读取Excel
string strSheetName = sheetName + "$";
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn);
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable dtTable = dtSet.Tables[0];
dataAdapter.Dispose();
return dtTable;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return null;
}
}
/// <summary>
/// 获取Excel的内容
/// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable
/// 的列标题
/// </summary>
/// <returns></returns>
public static System.Data.DataTable GetExcelTable(string excelPath)
{
return GetExcelTable(excelPath, false);
}
/// <summary>
/// 获取Excel的内容
/// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable
/// 的列标题
/// </summary>
/// <param name="excelPath"></param>
/// <param name="HasColumn">是否把一行做表头</param>
/// <returns></returns>
public static System.Data.DataTable GetExcelTable(string excelPath, bool HasColumn)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = null;
if (HasColumn == true)
{
conn = OpenExcelEx(excelPath, out connString); //连接
}
else
{
conn = OpenExcel(excelPath, out connString); //连接
}
//读取Excel
//string strSheetName = GetFirstSheetName(connString);//第一页如果改成其他名称，eg “名称”，sheet2，sheet3，则第一页为sheet2，还需要判断是否有内容
List<string> lstName = GetFirstSheetNames(connString);//对所有图层遍历看是否有内容
System.Data.DataTable dtTable = null;
foreach (string strSheetName in lstName)
{
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable table = dtSet.Tables[0];
dataAdapter.Dispose();
if (table.Columns.Count > 1)
{
dtTable = table;
break;
}
}
conn.Close(); //进程就不会被占用
return dtTable;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return null;
}
}
/// <summary>
/// 获取Excel的内容
/// 备注：读取EXCEL从单元格(N,M)读取到单元格(X,Y)，N,M,X,Y由strFilter指定,如：A3:C65535
/// </summary>
/// <param name="excelPath"></param>
/// <param name="HasColumn">是否把第N行做为列名（表头）</param>
/// <returns></returns>
public static System.Data.DataTable GetExcelTable(string excelPath, string strFilter, bool HasColumn)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = null;
if (HasColumn == true)
{
conn = OpenExcelEx(excelPath, out connString); //连接
}
else
{
conn = OpenExcel(excelPath, out connString); //连接
}
//读取Excel
string strSheetName = GetFirstSheetName(connString) + strFilter;
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable dtTable = dtSet.Tables[0];
dataAdapter.Dispose();
conn.Close(); //cql 进程就不会被占用
return dtTable;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return null;
}
}
/// <summary>
/// 表单以数值开头的处理方式，湖南项目
/// </summary>
/// <param name="excelPath"></param>
/// <param name="HasColumn"></param>
/// <returns></returns>
public static System.Data.DataTable GetNumExcelTable(string excelPath,int line, bool HasColumn)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = null;
if (HasColumn == true)
{
conn = OpenExcelEx(excelPath, out connString); //连接
}
else
{
conn = OpenExcel(excelPath, out connString); //连接
}
//读取Excel
List<string> _strSheetName = GetFirstSheetNames(connString);
string strSheetName = SelectSheet(_strSheetName);
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable dtTable = dtSet.Tables[0];
dataAdapter.Dispose();
conn.Close(); //进程就不会被占用
ClearNullTable(dtTable);
System.Data.DataTable newDt = dtTable.Clone();
DataRow row = null;
for (int i = line-1; i < dtTable.Rows.Count; i++)
{
newDt.Rows.Add(dtTable.Rows[i].ItemArray);
}
return newDt;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return null;
}
}
//去除空行空列
public static void ClearNullTable(System.Data.DataTable table)
{
bool bNull = true;
for (int i = 0; i < table.Rows.Count; i++)
{
bNull = true;
for (int j = 0; j < table.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString()))
{
bNull = false;
}
}
if (bNull)
{
table.Rows.RemoveAt(i);
i--;
}
}
for (int j = 0; j < table.Columns.Count; j++)
{
bNull = true;
for (int i = 0; i < table.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString()))
{
bNull = false;
}
}
if (bNull)
{
table.Columns.RemoveAt(j);
j--;
}
}
}
/// <summary>
/// 去除列全部为空的行
/// </summary>
/// <param name="table"></param>
public static void ClearNullRows(System.Data.DataTable table)
{
bool bNull = true;
for (int i = 0; i < table.Rows.Count; i++)
{
bNull = true;
for (int j = 0; j < table.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString()))
{
bNull = false;
}
}
if (bNull)
{
table.Rows.RemoveAt(i);
i--;
}
}
}
/// <summary>
/// 获取第一个Sheet页的名称
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
private static string GetFirstSheetName(string connectionString)
{
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
connection.Open();
System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string tableName = schemaTable.Rows[0][2].ToString().Trim();
connection.Close();
return tableName;
}
}
private static List<string> GetFirstSheetNames(string connectionString)
{
List<string> lstName = new List<string>();
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
connection.Open();
System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
foreach (DataRow row in schemaTable.Rows)
{
string tableName = row[2].ToString().Trim();
lstName.Add(tableName);
}
connection.Close();//
}
return lstName;
}
#region 直接打开读取Excel获取第一个页
//private static string GetFirstSheetNameEx(string filePath)
//{
// Application xApp = null;
// Workbook xBook = null;
// try
// {
// xApp = new ApplicationClass();
// xBook = xApp.Application.Workbooks.Open(filePath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//新建文件的代码
// string name = ((Worksheet)xBook.Sheets[0]).Name;
// return name;
// }
// catch(Exception ex)
// {
// LogHelper.Error.Append(ex);
// }
// finally
// {
// if (xBook != null)
// {
// xBook.Close(Type.Missing, Type.Missing, Type.Missing);
// xBook = null;
// xApp.Workbooks.Close();
// xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
// xApp = null;
// }
// }
// return "";
//}
#endregion
#endregion
/// <summary>
/// 判断文件是否正在使用
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static bool IsFileInUse(string fileName)
{
bool inUse = true;
if (File.Exists(fileName))
{
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.None);
inUse = false;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
}
finally
{
if (fs != null)
{
fs.Close();
}
}
return inUse; //true表示正在使用,false没有使用
}
else
{
return false; //文件不存在则一定没有被使用
}
}
public static System.Data.DataTable GetExcelTable(string excelPath, string strFilter, string strSheetName, bool HasColumn)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = null;
if (HasColumn == true)
{
conn = OpenExcelEx(excelPath, out connString); //连接
}
else
{
conn = OpenExcel(excelPath, out connString); //连接
}
//读取Excel
strSheetName = strSheetName + "$" + strFilter;
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable dtTable = dtSet.Tables[0];
dataAdapter.Dispose();
conn.Close(); //进程就不会被占用
return dtTable;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return null;
}
}
#region 写Excel
/// <summary>
/// 创建Excel进程
/// </summary>
/// <returns></returns>
private Application GetExcelApp()
{
Application excelApp = new Application();
excelApp.Application.Workbooks.Add(true);
_excelApp = excelApp;
return excelApp;
}
/// <summary>
/// 把DataTable的内容写入Excel
/// </summary>
/// <param name="strExcelPath">excel文件的路径</param>
/// <param name="htDataTable">key：sheetName，value：DataTable</param>
/// <returns></returns>
public bool WriteExcel(string strExcelPath, Hashtable htDataTable)
{
if (htDataTable == null || htDataTable.Count == 0)
{
return false;
}
bool writeRst = false;
try
{
if (_excelApp == null)
{
GetExcelApp();
}
//依次写入Sheet页
int countNum = 1;
foreach (DictionaryEntry de in htDataTable)
{
string sheetName = de.Key.ToString();
System.Data.DataTable dtTable = (System.Data.DataTable)de.Value;
Worksheet excelSheet = null;
if (countNum == 1)
{
excelSheet = (Worksheet)_excelApp.Worksheets[countNum];
}
else
{
excelSheet = (Worksheet)_excelApp.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
excelSheet.Name = sheetName;
bool sheetRst = dtTable.Columns.Contains("数据路径") ? writeSheet(excelSheet, dtTable, dtTable.Columns["数据路径"].Ordinal) : writeSheet(excelSheet, dtTable);
if (!sheetRst)
{
throw new Exception(sheetName + "创建失败！");
}
countNum++;
}
//保存
_excelApp.Visible = false;
_excelApp.DisplayAlerts = false;
_excelApp.AlertBeforeOverwriting = false;
_excelApp.ActiveWorkbook.SaveAs(strExcelPath, Type.Missing, null, null, false, false,
XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
writeRst = true;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
writeRst = false;
}
finally
{
//关闭Excel进程
object missing = System.Reflection.Missing.Value;
_excelApp.ActiveWorkbook.Close(missing, missing, missing);
_excelApp.Quit();
_excelApp = null;
//垃圾回收
GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
}
return writeRst;
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 销毁Excel
/// </summary>
/// <param name="excelApp"></param>
/// <param name="excelWorkbook"></param>
/// <param name="excelWorksheet"></param>
private static void DisposeExcelCOMObject(ref Microsoft.Office.Interop.Excel.Application excelApp, Microsoft.Office.Interop.Excel.Workbook excelWorkbook, Microsoft.Office.Interop.Excel.Worksheet excelWorksheet)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
excelWorksheet = null;
excelWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
excelApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
IntPtr t = new IntPtr(excelApp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
if (null != excelApp)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
}
System.GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
}
/// <summary>
/// 写Sheet页
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="dtTable"></param>
/// <returns></returns>
private bool writeSheet(Worksheet excelSheet, System.Data.DataTable dtTable, int Links)
{
if (excelSheet == null || dtTable == null)
{
return false;
}
//列名
for (int i = 0; i < dtTable.Columns.Count; i++)
{
DataColumn dtColumn = dtTable.Columns[i];
string caption = dtColumn.Caption;
excelSheet.Cells[1, i + 1] = caption;
}
//写入值
for (int i = 0; i < dtTable.Rows.Count; i++)
{
for (int j = 0; j < dtTable.Columns.Count; j++)
{
object objValue = dtTable.Rows[i][j];
excelSheet.Cells[2 + i, j + 1] = objValue;
if (Links.Equals(j))
excelSheet.Hyperlinks.Add(excelSheet.Cells[2 + i, j + 1], objValue.ToString(), Type.Missing, Type.Missing, Type.Missing);
}
}
excelSheet.Columns.AutoFit();
return true;
}
private bool writeSheet(Worksheet excelSheet, System.Data.DataTable dtTable)
{
return writeSheet(excelSheet, dtTable, -1);
}
#endregion
#region 打开Excel连接 ADO.Net
/// <summary>
/// 打开Excel文件,把一行做表头
/// 解决03版本和07版本Excel的访问驱动不兼容得问题
/// </summary>
/// <param name="excelPath">Excel文件全路径</param>
/// <param name="strConn">连接字符串</param>
/// <returns></returns>
public static OleDbConnection OpenExcelEx(string excelPath, out string strConn)
{
OleDbConnection conn = null;
//先用03版Excel连接方式
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn))
{
//在尝试用07的连接方式
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn)) return null;
}
return conn;
}
/// <summary>
/// 打开Excel文件
/// 解决03版本和07版本Excel的访问驱动不兼容得问题
/// </summary>
/// <param name="excelPath">Excel文件全路径</param>
/// <param name="strConn">连接字符串</param>
/// <returns></returns>
public static OleDbConnection OpenExcel(string excelPath, out string strConn)
{
OleDbConnection conn = null;
//先用03版Excel连接方式
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn))
{
//在尝试用07的连接方式
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn)) return null;
}
return conn;
}
/// <summary>
/// 打开Excel文件
/// </summary>
/// <param name="connectString">连接字符串</param>
/// <param name="conn">连接信息</param>
/// <returns></returns>
private static bool OpenExcelConnection(string connectString, out OleDbConnection conn)
{
conn = new OleDbConnection(connectString);
try
{
conn.Open();
return true;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return false;
}
}
#endregion
/// <summary>
/// Excel中插入值，保存，数值传输
/// </summary>
/// <param name="excelpath">保存路径</param>
/// <param name="sourcepath">excel模板路径</param>
/// <param name="insertTable"></param>
/// <returns></returns>
public static bool InsertExcelTable(string excelpath, string sourcepath, System.Data.DataTable insertTable, ExcelStatPara excelPar)
{
Application excelapp = new ApplicationClass();
Workbook mybook = null;
try
{
#region 不进行提示是否以只读方式打开
//mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
#endregion
Worksheet mysheet = null;
for (int i = 1; i <= mybook.Worksheets.Count; i++)
{
mysheet = (Worksheet)mybook.Worksheets[i];
if (mysheet.Name == excelPar.SheetName)
break;
}
bool bPicture = false;
if (string.IsNullOrEmpty(excelPar.PicturePath) == false && File.Exists(excelPar.PicturePath))//存在图片
{
bPicture = true;
}
//mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[3];
//mysheet.Columns.AutoFit();
//excelapp.Cells[excelPar.DateX, excelPar.DateY] = excelPar.StatDate;
if (excelPar.StatDate != null && string.IsNullOrEmpty(excelPar.StatDate) == false)
{
mysheet.Cells[excelPar.DateX, excelPar.DateY] = excelPar.StatDate;
}
int startLine = excelPar.StartLine;
Range range = null;
for (int i = startLine; i < insertTable.Rows.Count + startLine; i++) //第一列日期
{
if (bPicture == true)
{
range = null;
range = (Range)mysheet.Rows[i, Missing.Value];
range.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);//插入空行
}
for (int j = 1; j < insertTable.Columns.Count + 1; j++)
{
//excelapp.Cells[i, j] = insertTable.Rows[i - startLine][j - 1].ToString();//加行了用这个无效
mysheet.Cells[i, j] = insertTable.Rows[i - startLine][j - 1].ToString();
}
}
if (bPicture == true)
{
mysheet.Select(Missing.Value);
range = null;
string position = excelPar.PicPosition;
position = position.Substring(0, 1) + (Convert.ToInt32(position.Substring(1)) + insertTable.Rows.Count).ToString();
range = mysheet.get_Range(position, Missing.Value);
range.Select();//不选插入图片会乱，先mysheet.Select(),要不会出错
Pictures pics = (Pictures)mysheet.Pictures(Missing.Value);
pics.Insert(excelPar.PicturePath, Missing.Value);
}
mybook.SaveCopyAs(excelpath);
mybook.Close(false, Missing.Value, false);
excelapp.DisplayAlerts = false;//很重要，要不删除sheet不成功
//删除其他标签
mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= mybook.Sheets.Count; i++)
{
mysheet = (Worksheet)mybook.Worksheets[i];
if (mysheet.Name != excelPar.SheetName)
{
mysheet.Delete();
i--;
}
}
excelapp.DisplayAlerts = true;//
mybook.Save();
mybook.Close(false, Missing.Value, false);
excelapp.Workbooks.Close();
excelapp.Quit();
return true;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return false;
}
//finally
//{
// if (mybook != null)
// mybook.Close(false, Missing.Value, false);
// excelapp.Workbooks.Close();
// excelapp.Quit();
//}
}
/// <summary>
/// 根据模板导出
/// </summary>
/// <param name="excelpath">文件输出全路径</param>
/// <param name="sourcepath">模板文件全路径</param>
/// <param name="insertTable">需要插入的dataTable</param>
/// <param name="excelPar">关于excel控制参数，如：从哪一个开始填充数据，sheet的名称</param>
/// <returns>wcj</returns>
public static bool InsertExcelTable1(string excelpath, string sourcepath, System.Data.DataTable insertTable, ExcelStatPara excelPar)
{
Application excelapp = new ApplicationClass();
Workbook mybook = null;
try
{
mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Worksheet mysheet = null;
for (int i = 1; i <= mybook.Worksheets.Count; i++)
{
mysheet = (Worksheet)mybook.Worksheets[i];
if (mysheet.Name == excelPar.SheetName)
break;
}
int startLine = excelPar.StartLine;
Range range = null;
for (int i = startLine; i < insertTable.Rows.Count + startLine; i++) //从第6行开始
{
for (int j = 1; j < insertTable.Columns.Count - 1; j++) //+1 最后的两个字段不在excel中显示
{
mysheet.Cells[i, j] = insertTable.Rows[i - startLine][j].ToString();
}
}
mybook.SaveCopyAs(excelpath);
mybook.Close(false, Missing.Value, false);
excelapp.DisplayAlerts = false;//很重要，要不删除sheet不成功
//删除其他sheet标签
mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= mybook.Sheets.Count; i++)
{
mysheet = (Worksheet)mybook.Worksheets[i];
if (mysheet.Name != excelPar.SheetName)
{
mysheet.Delete();
i--;
}
}
excelapp.DisplayAlerts = true;//
mybook.Save();
mybook.Close(false, Missing.Value, false);
excelapp.Workbooks.Close();
excelapp.Quit();
return true;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return false;
}
}
/// <summary>
/// Excel中插入值，保存，数值传输
/// </summary>
/// <param name="excelpath">保存路径</param>
/// <param name="sourcepath">excel模板路径</param>
/// <param name="insertTable"></param>
/// <returns></returns>
public bool InsertExcelTable(string excelpath, string sourcepath, System.Data.DataTable insertTable)
{
try
{
Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook mybook;
#region 为了解决在进行汇总表导入过程中，不进行提示是否以只读方式打开,赖鸿祥
//mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
#endregion
Microsoft.Office.Interop.Excel.Worksheet mysheet;
mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
mysheet.Columns.AutoFit();
int sum = 0;
double dsum = 0.0;
excelapp.Cells[4, 1] = "合计";
for (int k = 5; k <= insertTable.Rows.Count + 4; k++) //第一列日期
{
excelapp.Cells[k, 1] = insertTable.Rows[k - 5][0].ToString();
}
for (int j = 2; j <= mysheet.UsedRange.Columns.Count; j++) //数值
{
sum = 0;
dsum = 0.0;
for (int i = 5; i <= insertTable.Rows.Count + 4; i++)
{
if (j <= 9)
{
sum += int.Parse(insertTable.Rows[i - 5][j - 1].ToString());
}
else
{
dsum += double.Parse(insertTable.Rows[i - 5][j - 1].ToString());
}
excelapp.Cells[i, j] = insertTable.Rows[i - 5][j - 1].ToString();
}
if (j <= 9)
{
excelapp.Cells[4, j] = sum;
}
else
{
excelapp.Cells[4, j] = dsum;
}
}
mybook.SaveCopyAs(excelpath);
mybook.Close(false, Missing.Value, false);
excelapp.Workbooks.Close();
excelapp.Quit();
return true;
}
catch (Exception ex)
{
LogHelper.Error.Append(ex);
return false;
}
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="excelpath"></param>
/// <param name="excelPar"></param>
/// <returns></returns>
public static bool InsertPicture(string excelpath, ExcelStatPara excelPar)
{
Application excelapp = new ApplicationClass();
Workbook mybook;
mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Worksheet mysheet = null;
for (int i = 1; i <= mybook.Worksheets.Count; i++)
{
mysheet = (Worksheet)mybook.Worksheets[i];
if (mysheet.Name == excelPar.SheetName)
break;
}
Range range = mysheet.get_Range(excelPar.PicPosition, Missing.Value);
range.Select();
Pictures pics = (Pictures)mysheet.Pictures(Missing.Value);
pics.Insert(excelPar.PicturePath, Missing.Value);
mybook.Save();
mybook.Close(false, Missing.Value, false);
excelapp.Workbooks.Close();
excelapp.Quit();
return true;
}
public static string SelectSheet(List<string> _strSheetName)
{
DevExpress.XtraEditors.XtraForm Form = new DevExpress.XtraEditors.XtraForm();
DevExpress.XtraEditors.ComboBoxEdit cmbSelect = new DevExpress.XtraEditors.ComboBoxEdit();
cmbSelect.Location = new System.Drawing.Point(29, 28);
cmbSelect.Size = new System.Drawing.Size(223, 21);
DevExpress.XtraEditors.BaseButton btnOK = new DevExpress.XtraEditors.BaseButton();
btnOK.Location = new System.Drawing.Point(205, 56);
btnOK.Size = new System.Drawing.Size(47, 23);
btnOK.Text = "选择";
btnOK.Click += new EventHandler(delegate(object sender, EventArgs e) { Form.Close(); });
Form.AutoScaleDimensions = new System.Drawing.SizeF(7F, 14F);
Form.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
Form.ClientSize = new System.Drawing.Size(284, 90);
Form.Controls.Add(cmbSelect);
Form.Controls.Add(btnOK);
Form.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
Form.MaximizeBox = false;
Form.MinimizeBox = false;
Form.ShowIcon = false;
Form.ShowInTaskbar = false;
Form.ControlBox = false;
Form.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
Form.Text = "选择Sheet页";
Form.TopMost = true;
cmbSelect.Properties.Items.AddRange(_strSheetName);
Form.ShowDialog();
return cmbSelect.SelectedItem.ToString();
}
}
/// <summary>
/// Excel结构类
/// </summary>
public class ExcelStatPara
{
public ExcelStatPara()
{
}
string _sheetName = ""; //Sheet名称
public string SheetName
{
get { return _sheetName; }
set { _sheetName = value; }
}
int _dateX;//日期位置第几行 ：I3为(3,9)
public int DateX
{
set { _dateX = value; }
get { return _dateX; }
}
int _dateY;//日期位置第几列
public int DateY
{
set { _dateY = value; }
get { return _dateY; }
}
int _startLine; //表格内容开始行
public int StartLine
{
set { _startLine = value; }
get { return _startLine; }
}
string _title = "";//标题
public string Title
{
set { _title = value; }
get { return _title; }
}
string _statDate = "";//日期
public string StatDate
{
set
{
_statDate = value;
}
get { return _statDate; }
}
string _strFilter = "";//表头范围 eg"A5:J6";
public string StrFilter
{
set { _strFilter = value; }
get { return _strFilter; }
}
System.Data.DataTable _resultTable = null;//查询结果
public System.Data.DataTable ResultTable
{
set
{
_resultTable = value;
}
get { return _resultTable; }
}
string _picturePath = ""; //有图片的话 存储位置
public string PicturePath
{
get { return _picturePath; }
set { _picturePath = value; }
}
string _picPosition = "";//图片位置，eg:A2
public string PicPosition
{
get { return _picPosition; }
set { _picPosition = value; }
}
}
