在将数据转储到excel时能找到我的错误吗? [英] Can any find my mistake while dumping the data into excel ?

问题描述

大家好！我有两个表，必须将信息转储到Excel中.我使用了下面的代码.但是却收到错误消息索引超出范围".

Hi everyone!I have a two table''s ,where i have to dump the info into the Excel.I have used the follwing code.But am getting the Error "Index out of Bound".

public void ConvertToTable()
    {
        DataSet ds = new DataSet();
        DataTable dt1 = new DataTable();
        DataTable dt2 = new DataTable();
        dt1 = ExportToExcel.ToDataTable<CMInfo>(CMInfo);
        dt2 = ExportToExcel.ToDataTable<RefMInfo>(REFMINFO);
        dt1.TableName = "Changed Method Information";
        dt2.TableName = "Referenced Method Information";
        ds.Tables.Add(dt1);
        ds.Tables.Add(dt2);
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet[] xlWorkSheet = new Excel.Worksheet[ds.Tables.Count];
        int Sheet_Count = 0;
        object misValue = System.Reflection.Missing.Value;
        xlApp = new Excel.Application();
        xlApp.SheetsInNewWorkbook = ds.Tables.Count;
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        foreach (DataTable dt in ds.Tables)
        {
            if (Sheet_Count < ds.Tables.Count)
            {
                xlWorkSheet[Sheet_Count] = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(Sheet_Count + 1);
                xlWorkSheet[Sheet_Count].Name = dt.TableName;
                int iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    xlWorkSheet[Sheet_Count].Cells[1, iCol] = c.ColumnName;
                    xlWorkSheet[Sheet_Count].Cells[1, iCol].Font.Bold = true;
                }
                // For each row of data
                int iRow = 0;
                foreach (DataRow r in dt.Rows)
                {
                    iRow++;
                    // Add each row''s cell data...
                    iCol = 0;
                    foreach (DataColumn c in dt.Columns)
                    {
                        iCol++;
                        xlWorkSheet[Sheet_Count].Cells[iRow + 1, iCol] = r[c.ColumnName];
                        xlWorkSheet[Sheet_Count].Cells.ColumnWidth = (xlWorkSheet[Sheet_Count].Cells[iRow + 1, iCol].Count() * 50);
                    }
                }
                Sheet_Count++;
            }

        }
        xlWorkBook.SaveAs("ReferenceMethodInfo.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();
        Marshal.ReleaseComObject(xlWorkSheet[Sheet_Count]);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);
        ds.Clear();
        System.Windows.Forms.MessageBox.Show("Exported Completed");
    }

//用于将列表转换为DataTable

//This for Converting the List to DataTable

 public static class ExportToExcel
{
    // remove "this" if not on C# 3.0 / .NET 3.5
    public static DataTable ToDataTable<T>(this IList<T> data)
    {
        PropertyDescriptorCollection props =
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();

        for (int i = 0; i < props.Count; i++)
        {
            PropertyDescriptor prop = props[i];
            table.Columns.Add(prop.Name, prop.PropertyType);
        }
        object[] values = new object[props.Count];
        foreach (T item in data)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = props[i].GetValue(item);
            }
            table.Rows.Add(values);
        }
        return table;
    }
}

推荐答案

我得到了我的解决方案.我在这里犯了一个错误.

I got My Solution .I did a mistake here .

for(int j=0;j< ds.Tables.Count;j++)
{
 Marshal.ReleaseComObject(xlWorkSheet[j]);
}

这篇关于在将数据转储到excel时能找到我的错误吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！