using System;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using OfficeExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Collections;
namespace BenQGuru.eSCM.WebOrder.ExcelLogic
{
public class ExcelOperation
{
/// <summary>
/// 将Excel数据导入到DataSet中
/// </summary>
/// <param name = "excelPath">Excel路径</param>
/// <returns></returns>
public DataSet GetExcelData(string excelPath)
{
OleDbConnection oleConn = null;
DataSet excelDataSet = new DataSet();
try
{
string connString = "Provider = Microsoft.Jet.OleDb.4.0;" + "data source = "
+ excelPath.Trim() + ";Extended Properties = 'Excel 8.0;HDR = Yes;IMEX = 1'";
oleConn = new OleDbConnection(connString);
oleConn.Open();
DataTable schemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim().ToUpper();
string sqlCondition = "SELECT * FROM [" + tableName + "] ";
OleDbCommand oleCmd = new OleDbCommand(sqlCondition, oleConn);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = oleCmd;
oleAdapter.Fill(excelDataSet);
}
catch
{
throw;
}
finally
{
oleConn.Close();
}
return excelDataSet;
}
/// <summary>
/// 根据的DataTable生成Excel
/// </summary>
/// <param name="templateFilePath">模版文件路径</param>
/// <param name="outputSheetIndex">输出到第几个Sheet</param>
/// <param name="outputFilePath">生成Excel文件路径</param>
/// <param name="targetSource">数据源DataTable</param>
public void ExportDataTableToExcel(string templateFilePath,int outputSheetIndex, string outputFilePath, DataTable targetSource)
{
File.Copy(templateFilePath, outputFilePath);
#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
object missing = Missing.Value;
//选择Excel的作用域
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
excelApp.AlertBeforeOverwriting = false;
excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
try
{
OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
for (int i = 0; i < targetSource.Rows.Count; i++)
{
for (int j = 0; j < targetSource.Columns.Count; j++)
{
xItems.Cells[i + 2, j + 1] = targetSource.Rows[i][j].ToString();
}
}
workBook.Save();
}
catch
{
throw;
}
finally
{
//清除资源,释放内存
workBook.Close(missing, missing, missing);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
GC.Collect();
}
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="templateFilePath"></param>
/// <param name="outputSheetIndex"></param>
/// <param name="outputFilePath"></param>
/// <param name="hsHead">key为excel中写入的位置,value为在位置写入的值</param>
/// <param name="iDetailStart"></param>
/// <param name="dtDetail"></param>
public void ExportDataTableToExcel(string templateFilePath,int outputSheetIndex, string outputFilePath,Hashtable hsHead,int iDetailStart,DataTable dtDetail,string exportColumnNames)
{
File.Copy(templateFilePath, outputFilePath);
#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
object missing = Missing.Value;
//选择Excel的作用域
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
excelApp.AlertBeforeOverwriting = false;
excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
try
{
OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
//写入主档
string _strKey = "";
string _strValue = "";
foreach(DictionaryEntry de in hsHead)
{
_strKey = de.Key.ToString();
_strValue = de.Value.ToString();
string[] _cellIndexs = _strKey.Split(',');
xItems.Cells[int.Parse(_cellIndexs[0]),int.Parse(_cellIndexs[1])] = _strValue;
}
string[] _arrColumnNames = exportColumnNames.Split(',');
//写入明细
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
for (int j = 0; j < _arrColumnNames.Length; j++)
{
xItems.Cells[iDetailStart + i + 2, j + 1] = dtDetail.Rows[i][_arrColumnNames[j].ToString()].ToString();
}
}
workBook.Save();
}
catch
{
throw;
}
finally
{
//清除资源,释放内存
workBook.Close(missing, missing, missing);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
GC.Collect();
}
#endregion
}
/// <summary>
/// 根据的DataTable生成Excel
/// </summary>
/// <param name="templateFilePath">模版文件路径</param>
/// <param name="outputSheetIndex">输出到第几个Sheet</param>
/// <param name="outputFilePath">生成Excel文件路径</param>
/// <param name="targetSource">数据源DataTable</param>
/// /// <param name="targetHead">excel表头的数据源Hashtable</param>
public void ExportDataTableToExcel(string templateFilePath,int outputSheetIndex, string outputFilePath, DataTable targetSource,Hashtable targetHead)
{
File.Copy(templateFilePath, outputFilePath);
#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
object missing = Missing.Value;
//选择Excel的作用域
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
excelApp.AlertBeforeOverwriting = false;
excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
try
{
OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
for (int i = 0; i < targetHead.Count; i++)//保存表头
{
xItems.Cells[1, i + 1] = targetHead[i].ToString();
}
for (int i = 0; i < targetSource.Rows.Count; i++)
{
for (int j = 0; j < targetSource.Columns.Count; j++)
{
xItems.Cells[i + 2, j + 1] = targetSource.Rows[i][j].ToString();
}
}
workBook.Save();
}
catch
{
throw;
}
finally
{
//清除资源,释放内存
workBook.Close(missing, missing, missing);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
GC.Collect();
}
#endregion
}
//add by jodie
/// <summary>
/// 根据的DataTable生成Excel (for Maintain_Liablity页面)
/// </summary>
/// <param name="templateFilePath">模版文件路径</param>
/// <param name="outputSheetIndex">输出到第几个Sheet</param>
/// <param name="outputFilePath">生成Excel文件路径</param>
/// <param name="targetSource">数据源DataTable</param>
public void ExportDataTableToExcelForMaintainLiability(string templateFilePath, int outputSheetIndex, string outputFilePath, DataTable targetSource)
{
File.Copy(templateFilePath, outputFilePath);
#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
object missing = Missing.Value;
//选择Excel的作用域
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
excelApp.AlertBeforeOverwriting = false;
excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
try
{
int iColor = System.Drawing.Color.Yellow.ToArgb(); //颜色转换,转成int
OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
for (int i = 0; i < targetSource.Rows.Count; i++)
{
for (int j = 0; j < targetSource.Columns.Count; j++)
{
if(j < targetSource.Columns.Count-1)
{
xItems.Cells[i + 2, j + 1] = targetSource.Rows[i][j].ToString();
if (targetSource.Rows[i]["FLAG"].ToString().Trim() == "1")
{
xItems.get_Range(xItems.Cells[i + 2, j + 1], xItems.Cells[i + 2, j + 1]).Interior.Color = iColor;
//xItems.get_Range(xItems.Cells[i + 2, j + 1], xItems.Cells[i + 2, j + 1]).Interior.Color = 11;
}
}
}
}
workBook.Save();
}
catch(Exception ex)
{
throw;
}
finally
{
//清除资源,释放内存
workBook.Close(missing, missing, missing);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
GC.Collect();
}
#endregion
}
//end add by jodie
#region 导出账款查询的数据
/// <summary>
/// 根据的DataTable生成Excel
/// 重写by fly xia 2010-11-24
/// </summary>
/// <param name="templateFilePath">模版文件路径</param>
/// <param name="outputSheetIndex">输出到第几个Sheet</param>
/// <param name="outputFilePath">生成Excel文件路径</param>
/// <param name="targetSource">数据源DataTable</param>
public void ExportDataTableToExcelWithStyle(string templateFilePath, int outputSheetIndex, string outputFilePath, DataTable targetSource, System.Collections.Hashtable htStyle)
{
File.Copy(templateFilePath, outputFilePath);
#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
object missing = Missing.Value;
//选择Excel的作用域
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
excelApp.AlertBeforeOverwriting = false;
excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
try
{
//获取样式数据
int _intTitleRow = 1;
int _intGridTitleRow = 5;
int _intBgColor = 14;
int _intFontColor = 53;
string _strTitle = "阿里斯顿热能产品(中国)有限公司对账单";
if (htStyle.Count > 0)
{
if (htStyle["titlerow"] != null)
{
_intTitleRow = int.Parse(htStyle["titlerow"].ToString());
if (htStyle["row"] != null)
_intGridTitleRow = int.Parse(htStyle["row"].ToString());
//if (htStyle["bgcolor"] != null)
// _intBgColor = int.Parse(htStyle["bgcolor"].ToString());
if (htStyle["fontcolor"] != null)
_intFontColor = int.Parse(htStyle["fontcolor"].ToString());
}
if (htStyle["title"] != null)
{
_strTitle = htStyle["title"].ToString();
}
}
//向Excel中写入数据
OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
for (int i = 0; i < targetSource.Rows.Count; i++)
{
for (int j = 0; j < targetSource.Columns.Count; j++)
{
xItems.Cells[i + _intTitleRow + 1, j + 1] = targetSource.Rows[i][j].ToString();
}
}
xItems.Cells[1, 1] = _strTitle;//标题
//设置样式
if (htStyle["titlerow"] != null && htStyle["row"] != null)
{
设置Grid标题行的样式
//for (int j = 0; j < targetSource.Columns.Count; j++)
//{
// if (htStyle["bgcolor"] != null)
// ((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 1, j + 1]).Interior.ColorIndex = _intBgColor;//背景颜色
// ((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 1, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
//}
//设置Grid内容的的特殊样式
if (htStyle["fontcolor"] != null && _intGridTitleRow < targetSource.Rows.Count-1)
{
//设置Grid标题行下一行的样式
((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 2, 4]).Font.ColorIndex = _intFontColor;
((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 2, 7]).Font.ColorIndex = _intFontColor;
//设置Grid最后一行的样式((targetSource.Rows.Count-1)+_intTitleRow+1)
((Microsoft.Office.Interop.Excel.Range)xItems.Cells[targetSource.Rows.Count + _intTitleRow, 4]).Font.ColorIndex = _intFontColor;
((Microsoft.Office.Interop.Excel.Range)xItems.Cells[targetSource.Rows.Count + _intTitleRow, 7]).Font.ColorIndex = _intFontColor;
}
//设置Grid内容的的一般样式
for (int i = _intGridTitleRow; i < targetSource.Rows.Count; i++)
{
for (int j = 0; j < targetSource.Columns.Count; j++)
{
((Microsoft.Office.Interop.Excel.Range)xItems.Cells[i + _intTitleRow + 2, j + 1]).Font.Size = 10;//字体大小
if(j<4)
((Microsoft.Office.Interop.Excel.Range)xItems.Cells[i + _intTitleRow + 2, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;//靠左
}
}
}
workBook.Save();
}
catch
{
throw;
}
finally
{
//清除资源,释放内存
workBook.Close(missing, missing, missing);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
GC.Collect();
}
#endregion
}
#endregion
}
}