Export an Excel file

Private void Invoke ()
{
ArrayList arrlist = new ArrayList ();
DataTable tab1 = UtlBiz. getdefadefatransaction (). GetTable ("select * from MERs ");
DataTable tab2 = UtlBiz. getdefadefatransaction (). GetTable ("select * from Employees ");
Arrlist. Add (tab1 );
Arrlist. Add (tab2 );
String webFileTmp = "Files \ aaa.xls"; // template path
String webFileTar = "Files \ ttt.xls"; // target path
String phyFileTmp = Server. MapPath (webFileTmp); // template path
String phyFileTar = Server. MapPath (webFileTar); // target path
ExcDataOnTmpA (arrlist, phyFileTmp, phyFileTar );
}
// ================================================ ======================================
// Export the file (the source file name and template file name are required)
// ================================================ ======================================
Private void ExcDataOnTmpA (ArrayList arrlist, string tmpFileName, string phyFileTar)
{
// Copy File to tarFileName
CopyFile (tmpFileName, phyFileTar );
ArrayList substItemList = GetSubstCellList (phyFileTar, 1,100,100 );
SubstituteCellData (arrlist, substItemList, phyFileTar );
}
// ================================================ ======================================
// Copy the Template File
// ================================================ ======================================
Private bool CopyFile (string phyFileTmp, string phyFileTar)
{
If (! File. Exists (phyFileTmp ))
Return false;
Try {
File. Copy (phyFileTmp, phyFileTar, true );
Return true;
} Catch (Exception ex ){
Return false;
}
}

// ================================================ ======================================
// Fill the cell and save the target file
// ================================================ ======================================
 
Private void SubstituteCellData (ArrayList arrList, ArrayList subsItemList, string phyFileTar)
{
Excel. Application excelApp = new Excel. ApplicationClass (); // create an Application Object

Excel. Workbook workBook = excelApp. Workbooks. _ Open (phyFileTar, Missing. Value, Missing. Value,
Missing. Value,
Missing. Value, Missing. Value );
Excel. Worksheet workSheet = (Excel. Worksheet) workBook. ActiveSheet;
Try {
// Traverse Cells
CopyExcelFormat (workSheet, arrList, subsItemList );
Foreach (SubstCell substCell in subsItemList) FillSubstCellData (workSheet, arrList, substCell); // fill the cell
WorkBook. Save ();
} Catch (Exception Er ){
Throw new Exception ("An error occurred while calling the EXCEL program! "+ Er. Message );
} Finally {
If (workBook! = Null) workBook. Close (false, Missing. Value, Missing. Value );
If (excelApp! = Null) excelApp. Quit ();
}
}
// ================================================ ======================================
// Verify whether the format is 0: MAmount: F and check whether multiple rows are recorded and fill in the data. T indicates multiple rows.
// ================================================ ======================================
Private void FillSubstCellData (Excel. Worksheet workSheet, ArrayList dataList, SubstCell substCellItem)
{
String cellStr = substCellItem. cellStr; // defined cell content
ArrayList strList = UtlMisc. GetItemsListFromString (cellStr, ":"); // separate the cell content ":"
Int dataIndex = int. Parse (strList [0]. ToString (); // obtain the number of tables in the dataset
String fieldName = strList [1]. ToString (); // obtain the field name in the table
Bool isList = strList [2]. ToString (). ToUpper () = "T"; // checks whether the record is a multi-row record.
If (isList) {// if it is a multi-row record
DataTable tbl = dataList [dataIndex] as DataTable; // converts a qualified data type to the datatable type.
// Copy the cell format
For (int I = 0; I <tbl. Rows. Count-1; I ++ ){
WorkSheet. Cells [substCellItem. rowIndex + I, substCellItem. coluIndex] = tbl. Rows [I] [fieldName];}
} Else {
WorkSheet. Cells [substCellItem. rowIndex, substCellItem. coluIndex] = (DataTable) dataList [dataIndex]). Rows [0] [fieldName];
}
}
// ================================================ ======================================
// Copy the format
// ================================================ ======================================
Private void CopyExcelFormat (Excel. Worksheet workSheet, ArrayList dataList, ArrayList subsItemList)
{
Int copyRowCount = 0;
Foreach (SubstCell substCell in subsItemList ){
String cellStr = substCell. cellStr; // defined cell content
ArrayList strList = UtlMisc. GetItemsListFromString (cellStr, ":"); // separate the cell content ":"
Int dataIndex = int. Parse (strList [0]. ToString (); // obtain the number of tables in the dataset
String fieldName = strList [1]. ToString (); // obtain the field name in the table
Bool isList = strList [2]. ToString (). ToUpper () = "T"; // checks whether the record is a multi-row record.
If (isList) {// if it is a multi-row record
DataTable tbl = dataList [dataIndex] as DataTable;
Int rowsCount = tbl. Rows. Count;
If (rowsCount> copyRowCount) copyRowCount = rowsCount;
}
}
For (int I = 1; I <copyRowCount-1; I ++ ){
(Excel. Range) workSheet. Rows [7, Type. Missing]). Copy (Type. Missing );
(Excel. Range) workSheet. Cells [7 + I, 1]). EntireRow. Insert (0, 0 );
For (int j = 1; j <workSheet. UsedRange. Columns. Count + 3; j ++) workSheet. Cells [7 + I, j] = "";
}
}

// ================================================ ==================================
// The row, column, and cell of the template
// ================================================ ==================================

Public struct SubstCell
{
Public int rowIndex; // template row
Public int coluIndex; // template Column
Public string cellStr; // The cell data of the template.
}
// ================================================ ======================================
// Determine the format 0: MAmount: F
// ================================================ ======================================
Private bool IsDataFieldCell (string cellStr)
{
If (string. IsNullOrEmpty (cellStr) return false; // determines whether it is null or ""
ArrayList list = UtlMisc. GetItemsListFromString (cellStr, ":"); // intercept the string and separate it ":"
If (list. Count! = 3) return false; // determines whether the number of strings is 3.
If (! UtlMisc. IsIntString (list [0]. ToString () return false; // checks whether the first character is a number.
Return true;
}

// ================================================ ==================================
// Read the Excel file and retrieve the cell
// ================================================ ==================================

Private ArrayList GetSubstCellList (string phyFileTar, int sheetIndex, int rowCount, int columnCount)
{
Excel. ApplicationClass excelApp = new Excel. ApplicationClass ();
// Open the template file to obtain the WorkBook object.
Excel. Workbooks workBooks = excelApp. Workbooks;
Excel. Workbook workBook = workBooks. _ Open (phyFileTar, Missing. Value, Missing. Value
, Missing. Value
, Missing. Value );
ArrayList list = new ArrayList ();
Try {
// Obtain the worksheet object
Excel. Worksheet workSheet = (Excel. Worksheet) workBook. Sheets [sheetIndex]; // locate the expected sheet
If (workSheet = null) return null;
If (rowCount = 0) rowCount = workSheet. UsedRange. Rows. Count; // you can specify the number of cells in use.
If (columnCount = 0) columnCount = workSheet. UsedRange. Columns. Count; // you can specify the number of Columns in used cells.
For (int I = 1; I <= rowCount; ++ I)
For (int j = 1; j <= columnCount; ++ j ){
SubstCell cellItem = new SubstCell (); // create a Cell Object
CellItem. rowIndex = I;
CellItem. coluIndex = j;
Excel. Range range = (Excel. Range) workSheet. Cells [I, j]; // retrieves a cell
If (range. Value2! = Null)
CellItem. cellStr = range. Value2.ToString ();
If (IsDataFieldCell (cellItem. cellStr) list. Add (cellItem );
}
} Catch (Exception Er ){
Throw new Exception ("An error occurred while calling the EXCEL program! "+ Er. Message );
}
Finally {
If (workBook! = Null) workBook. Close (false, Missing. Value, Missing. Value );
If (workBooks! = Null) workBooks. Close ();
If (excelApp! = Null) excelApp. Quit ();
}
Return list;
}
}