数据集在C＃中表现优异（使用microsoft.office.interop.excel） [英] Dataset to excel in C# (using microsoft.office.interop.excel)

问题描述

抱歉，我们找不到E：\ Tst.xlsx。它是否可能被移动，重命名或删除？

Sorry, we couldn't find E:\Tst.xlsx. Is it possible it was moved, renamed or deleted?

DataTable dt = new DataTable();

//Add Datacolumn
DataColumn workCol = dt.Columns.Add("FirstName", typeof(String));           
dt.Columns.Add("Blog", typeof(String));
dt.Columns.Add("City", typeof(String));
dt.Columns.Add("Country", typeof(String));

//Add in the datarow
DataRow newRow = dt.NewRow();

newRow["firstname"] = "Faisal";            
newRow["Blog"] = "http://google.com/";
newRow["city"] = "Mysore";
newRow["country"] = "India";

dt.Rows.Add(newRow);

DataSet ds = new DataSet();
ds.Tables.Add(dt);



ExportDataSetToExcel(ds, "E:\\Tst.xlsx");


private void ExportDataSetToExcel(DataSet ds, string _filename)
{
    //Creae an Excel application instance
    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
    
    //Create an Excel workbook instance and open it from the predefined location
    //ERROR
    Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(_filename);
    
    
    foreach (DataTable table in ds.Tables)
    {
        //Add a new worksheet to workbook with the Datatable name
        Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
        excelWorkSheet.Name = table.TableName;
        
        for (int i = 1; i < table.Columns.Count + 1; i++)
        {
            excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
        }
        
        for (int j = 0; j < table.Rows.Count; j++)
        {
            for (int k = 0; k < table.Columns.Count; k++)
            {
             excelWorkSheet.Cells[j+2, k+1] = table.Rows[j].ItemArray[k].ToString();
            }
        }
    }

    excelWorkBook.Save();
    excelWorkBook.Close();
    excelApp.Quit();
}

推荐答案

首先创建一个文件E：\\Tst.xlsx。

因为您在代码中打开它，而不是创建错误。

Create a file E:\\Tst.xlsx first.
Because you are opening it in code, not creating so the error.

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
               Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
               Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
               object misValue = System.Reflection.Missing.Value;

               xlWorkBook = xlApp.Workbooks.Add(misValue);
               xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
               xlWorkSheet.Cells[1, 1] = "Sheet 1 content";

               xlWorkBook.SaveAs(_path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
               xlWorkBook.Close(true, misValue, misValue);
               xlApp.Quit();

               releaseObject(xlWorkSheet);
               releaseObject(xlWorkBook);
               releaseObject(xlApp);

private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

这篇关于数据集在C＃中表现优异（使用microsoft.office.interop.excel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！