/// <summary>
/// 功能：实现Excel应用程序的打开
/// </summary>
/// <param name="TemplateFilePath">模板文件物理路径</param>
public void Open(string TemplateFilePath)
{
//打开对象
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objExcel.Visible = false;
m_objExcel.DisplayAlerts = false;
if (m_objExcel.Version != "11.0")
{
MessageBox.Show("您的Excel 版本不是11.0 （Office 2003），操作可能会出现问题。");
m_objExcel.Quit();
return;
}
m_objBooks = (Workbooks)m_objExcel.Workbooks;
if (TemplateFilePath.Equals(String.Empty))
{
m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt));
}
else
{
m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
m_objSheets = (Sheets)m_objBook.Worksheets;
m_objSheet = (_Worksheet)(m_objSheets.get_Item(1));
m_objExcel.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
}
/// <summary>
/// 利用Excel的QueryTable导出数据
/// </summary>
/// <param name="intSheetNumber">导出第几个sheet</param>
/// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param>
/// <param name="strTitle">表头,需与查询sql语句对齐一致。</param>
/// <param name="strSql">查询的sql语句，表头的文字需与该sql语句对齐一致。</param>
/// <param name="strTablName">查询的表名</param>
/// <param name="strMastTitle">主标题</param>
/// </summary>
public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan, string[] strTitle, string strSql, string strTablName, string strMastTitle)
{
string strQuery = string.Empty;
if (blIsMoreThan)
{
strQuery = "Select Top " +
this.dbSheetSize + strSql + " From " + strTablName + " Where Not OrderID In (Select Top " +
dbSheetSize * (intSheetNumber - 1) + " OrderID From " + strTablName + ")";
}
else
{
strQuery = "Select Top " + this.dbSheetSize + strSql + " From " + strTablName;
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));
m_objSheet.Cells[1, 1] = strMastTitle;
m_objSheet.Cells[2, 1] = "打印日期" + DateTime.Now.ToShortDateString();
for (int i = 1; i <= strTitle.Length; i++)
{
m_objSheet.Cells[4, i] = strTitle[i - 1].ToString();
}
m_objRange = m_objSheet.get_Range("A5", m_objOpt);
m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery);
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.FieldNames = false;
m_objQryTable.Refresh(false);
}
/// <summary>
/// 新建一个Excel实例
/// </summary>
/// <param name="strTitle">Excel表头上的文字</param>
public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
{
m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = true;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
if (intSheetTotalSize <= 3)
{
if (this.dbTotalSize <= this.dbSheetSize)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
return;
}
else if (this.dbTotalSize <= this.dbSheetSize * 2)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
return;
}
else
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
return;
}
}
for (int i = 3; i < intSheetTotalSize; i++)
{
m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
}
ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
}
}
private void OpenTemplate()
{
try
{
InvoiceTemplate = new Excel.ApplicationClass();
InvoiceTemplate.Visible = false;
//string workbookPath = m_TemplatePath;
excelWorkbook = InvoiceTemplate.Workbooks.Open(m_TemplatePath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
InvoiceSheets = InvoiceTemplate.Worksheets;
string currentSheet = "Page1";
if (m_iInvoiceType == (int)InvoiceType.ShippingList)
{
currentSheet = "Page2";
}
excelWorksheet = (Excel.Worksheet)InvoiceSheets.get_Item(currentSheet);
}
catch (Exception ex)
{
throw(ex);
}
}
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(filePath));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
_excelSheet.Name = sheetName;
}
public void OpenExcelApp(bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
}