Excel報表輸出-講稿ppt課件

1、林淵鐘2022-4-17ExcelExcel對象模型對象模型對象根本操作對象根本操作ApplicationApplication對象對象WorkbooksWorkbooks對象對象WorksheetsWorksheets對象對象RangeRange對象對象ExcelExcel操作方法擴(kuò)展操作方法擴(kuò)展(VBA(VBA宏宏) )性能優(yōu)化性能優(yōu)化FastExportFastExportSingletonSingleton套用模板輸出套用模板輸出ExcelExcel動態(tài)輸出設(shè)計動態(tài)輸出設(shè)計創(chuàng)建釋放翻開封鎖設(shè)置當(dāng)前操作的sheet獲取Range : 單個: (Range)_workSheet.Cells

2、top, left; 區(qū)域: _workSheet.get_Range(“B3:D5, Type.Missing); _workSheet.get_Range(_workSheet.Cells3, 2,_workSheet.Cells5, 4);合并單元格: if(range.Count 1) range.Merge(Missing.Value);設(shè)置單元格值: range.NumberFormatLocal = “; /文本格式,“.0_一位小數(shù) range.Value2 = value; /區(qū)別range.Value格式化目的單元格: styleRange.Copy(Missing.Val

3、ue); objectRange.PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);序列填充: startRange.Value2 = “1; /填充第一個單元格序號1 startRange.AutoFill(destination, XlAutoFillType.xlFillSeries);設(shè)置公式: targetRange.Formula = “=SUM(B3:B10); targetRange.FormulaR1C1=“=S

4、UM(R-3C-2:R-1C-2,R-3C:R-1C);u工具-宏-錄制新宏u工具-宏-Visual Basic 編輯器Demo public void FastExportToExcel(System.Data.DataTable dt, int startTop, int startLeft) / Copy the DataTable to an object array object, rawData = new objectdt.Rows.Count, dt.Columns.Count; / Copy the values to the object array for (int ro

5、w = 0; row dt. Rows.Count; col+) for (int col = 0; col dt. Columns.Count; row+) rawDatarow, col = value; Range range = GetRange(startTop, startLeft, dt.Rows.Count, dt.Columns.Count); / Fast data export to Excel range.Value2 = rawData; 創(chuàng)建二維數(shù)組二維數(shù)組賦值直接填值轉(zhuǎn)置處置？轉(zhuǎn)置處置？耗費大量的內(nèi)存和時間耗費大量的內(nèi)存和時間 public class Excel

6、Operator private static ExcelOperator instance; private ApplicationClass _appClass = null; private _Workbook _workBook = null; private string _filePath = ; public string FileName get return _filePath; private ExcelOperator(string filePath) _appClass = new ApplicationClass(); _appClass.Visible = fals

7、e; Open(filePath); /翻開workbook 賦值_filePath = filePath public static ExcelOperator GetInstance(string filePath) if (instance = null) instance = new ExcelOperator(filePath); /保證僅實例化一次 return instance; 靜態(tài)模板靜態(tài)模板模板分類模板分類 動態(tài)模板動態(tài)模板1固定單元格填充CellInfo類2動態(tài)生成表頭僅動態(tài)Excel表3從數(shù)據(jù)庫獲取DataTable，快速填充CelleInfo類構(gòu)造函數(shù)：CellInf

8、o(string content, int top, int left)CellInfo(string content, int top, int left, int finalTop, int finalLeft)例：假設(shè)要合并B3-D5共9個單元格，并填寫“內(nèi)容兩個字，那么運(yùn)用CellInfo(“內(nèi)容,3,2,5,4)1.統(tǒng)計統(tǒng)計/刪除現(xiàn)有統(tǒng)計表刪除現(xiàn)有統(tǒng)計表DropTable(DataAccess GisDbAccess, string tableName);/ 根據(jù)字段根據(jù)字段lstItemInfo列表生成統(tǒng)計表列表生成統(tǒng)計表tableNameCreateTable(DataAcces

9、s GisDbAccess, string tableName, List lstItemInfo); /刪除統(tǒng)計的存儲過程刪除統(tǒng)計的存儲過程DropProcedure(DataAccess GisDbAccess, string procName);/創(chuàng)建統(tǒng)計的存儲過程創(chuàng)建統(tǒng)計的存儲過程string procString = GetGenerateStatProcSQL(); /存儲過程的存儲過程的SQL語句語句CreateStatProcedure(DataAccess GisDbAccess, string procString);/執(zhí)行統(tǒng)計的存儲過程執(zhí)行統(tǒng)計的存儲過程RunProced

10、ure(string storedProcName, IDataParameter parameters);2.獲取數(shù)據(jù)獲取數(shù)據(jù)/刪除獲取統(tǒng)計表數(shù)據(jù)的存儲過程刪除獲取統(tǒng)計表數(shù)據(jù)的存儲過程DropProcedure(DataAccess GisDbAccess, string procName);/創(chuàng)建獲取統(tǒng)計表數(shù)據(jù)的存儲過程創(chuàng)建獲取統(tǒng)計表數(shù)據(jù)的存儲過程string SQLString = GetGenerateSelectProcSQL(); /存儲過程的存儲過程的SQL語句語句ExecuteSql(string SQLString);/執(zhí)行獲取統(tǒng)計表數(shù)據(jù)的存儲過程執(zhí)行獲取統(tǒng)計表數(shù)據(jù)的存儲過程DataSet RunProcedure(string storedProcN