{
sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
sqlCmd = new System.Data.SqlClient.SqlCommand(\PD_WORKBIL_MST\ sqlConn.Open();
dbTotalSize = (int)sqlCmd.ExecuteScalar(); sqlConn.Close();
return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize); }
private void DeclareExcelApp() {
m_objExcel = new Excel.ApplicationClass();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; intSheetTotalSize = GetTotalSize(); if (intSheetTotalSize <= 3) {
if (this.dbTotalSize <= this.dbSheetSize) {
this.ExportDataByQueryTable(1, false); return;
}
else if (this.dbTotalSize <= this.dbSheetSize * 2) {
this.ExportDataByQueryTable(1, false); this.ExportDataByQueryTable(2, true); return; } else {
this.ExportDataByQueryTable(1, false); this.ExportDataByQueryTable(2, true); this.ExportDataByQueryTable(3, true); 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);
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(i, true); } }
private void SaveExcelApp() {
string excelFileName = string.Empty; SaveFileDialog sf = new SaveFileDialog(); sf.Filter = \
if (sf.ShowDialog() == DialogResult.OK) {
excelFileName = sf.FileName; } else {
return; }
m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); }
private void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan) {
string strQuery = string.Empty; if (blIsMoreThan) {
strQuery = \
this.dbSheetSize + \ CMPID In (Select Top \
dbSheetSize * (intSheetNumber-1) + \ CMPID From PD_WORKBIL_MST)\ } else {
strQuery = \\
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber)); m_objSheet.Cells[1,1] = \中文測試一\ m_objSheet.Cells[1,2] = \中文測試二\ m_objSheet.Cells[1,3] = \中文測試三\ m_objSheet.Cells[1,4] = \中文測試四\ m_objSheet.Cells[1,5] = \中文測試五\
m_objRange = m_objSheet.get_Range(\ m_objQryTable =
m_objSheet.QueryTables.Add(\strQuery);
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.FieldNames = false; m_objQryTable.Refresh(false); }
private void button1_Click(object sender, EventArgs e) {
DeclareExcelApp(); SaveExcelApp(); } } }
在本篇中﹐是將13萬多條記錄分多個Sheet導出。如果你的機器大概像我這樣:P4CPU,1G內存的話。全部導出包括保存也就是20秒左右就可以全部搞定。
也許大家會有更好的方法來實現﹐歡迎各位交流﹗