C#操作EXCEL补充

                int startedrow =WSheet.UsedRange.Row;//模板已使用区域的起始行
                int startedcol = WSheet.UsedRange.Column;//模板已使用区域的起始列
                if( rowCount == 0 ) rowCount = WSheet.UsedRange.Rows.Count + startedrow - 1;//模板已使用区域的行数
                if( columnCount == 0 ) columnCount = workSheet.UsedRange.Columns.Count + startedcol - 1;//模板已使用区域的列数

 这样就可以直接调用GetSubstCellList( phyFileTar, 1, 0, 0 );了。

 

  static private void CopyExcelFormat(Excel.Worksheet workSheet, ArrayList dataList, ArrayList subsItemList)
        {
            foreach( SubstCell substCell in subsItemList )
            {
            string cellStr = substCell.cellStr;      //定义的单元格内容
         ArrayList strList = UtlMisc.GetItemsListFromString( cellStr, ":" );  //单元格内容用":"隔开
        int dataIndex = int.Parse( strList[0].ToString() );   //取数据集中第几张表
                string fieldName = strList[1].ToString();    //取表中的字段名
                bool isList = strList[2].ToString().ToUpper() == "T";  //判断是否是多行记录
                if( isList ){
                    int r = substCell.rowIndex;
                    int c = substCell.coluIndex;
                    Excel.Range sRange = workSheet.get_Range(workSheet.Cells[r, c], workSheet.Cells[r, c]);
                    DataTable tbl = dataList[dataIndex] as DataTable;
                    int rCount = tbl.Rows.Count;
                    for( int inc = 1; inc < rCount; ++inc ){
                        bool isMerge = (bool)sRange.MergeCells;//判断是否为合并单元格
                        if( isMerge ){
                            int mergerow = sRange.MergeArea.Rows.Count;//得到合并单元格的横格数
                            int mergecol = sRange.MergeArea.Columns.Count;//得到合并单元格的列格数
                            Excel.Range tRange = workSheet.get_Range( workSheet.Cells[r + inc * mergerow, c], workSheet.Cells[r + (inc + 1) * mergerow - 1, c + mergecol - 1] );//合并单元格的区域
                            sRange.Copy( tRange );
                        }else{                      
                            Excel.Range tRange = workSheet.get_Range( workSheet.Cells[r + inc, c], workSheet.Cells[r + inc, c] );
                            sRange.Copy( tRange );
                            if ( copyRowCount < rCount ){//不加这一句最后一行会显示一个格式0:MAmount:F  
                                for ( int j = 1; j < workSheet.UsedRange.Columns.Count - 1 + c; j++ ) workSheet.Cells[inc + r, j] = "";
                            }
                        }
                    }
                }
            }
        }

 

  static private void FillSubstCellData( Excel.Worksheet workSheet, ArrayList dataList, SubstCell substCellItem )
        {
            string cellStr = substCellItem.cellStr;//定义的单元格内容
            ArrayList strList = UtlMisc.GetItemsListFromString( cellStr, ":" );  //单元格内容用":"隔开
            int dataIndex = int.Parse(strList[0].ToString()); //取数据集中第几张表
            string fieldName = strList[1].ToString(); //取表中的字段名
            bool isList = strList[2].ToString().ToUpper() == "T";  //判断是否是多行记录
            if(isList)
            {    //如果是多行记录     
                DataTable tbl = dataList[dataIndex] as DataTable;  //把符合条件的转换成datatable类型             
                int r = substCell.rowIndex;
                int c = substCell.coluIndex;
                Excel.Range sRange = workSheet.get_Range(workSheet.Cells[r, c], workSheet.Cells[r, c]);
                int rCount = tbl.Rows.Count;
                for(int i = 0; i < tbl.Rows.Count; i++)
                {
                    bool isMerge = (bool)sRange.MergeCells;//判断是否为合并单元格
                    if( isMerge ){
                        DataTable tb2 = dataList[dataIndex] as DataTable;
                        int mergerow = sRange.MergeArea.Rows.Count;//合并单元格区域的横格数
                        int mergecol = sRange.MergeArea.Columns.Count;//合并单元格的列格数
                        workSheet.Cells[r + i * mergerow, c] = tb2.Rows[i][fieldName];//为合并单元格赋值
                    }
                    else workSheet.Cells[r + i, c] = tbl.Rows[i][fieldName];
                }
            }
            else  workSheet.Cells[substCellItem.rowIndex, substCellItem.coluIndex] = ((DataTable)dataList[dataIndex]).Rows[0][fieldName];                   
        }