c# - 无法将数据表写入Excel c#

问题描述

我正在尝试将data table值保存到 excel 中。我有一个Load Report按钮，我从中获取数据到网格视图中。

加载报告按钮代码

private void BtnInvHD_Click(object sender, EventArgs e)
    {
        string dir = @"C:\Output\Log\HavingDuesLog";
        if (!Directory.Exists(dir))
        {
            Directory.CreateDirectory(dir);
        }
        string location = Path.Combine(dir, "HavingDues_log_"+name+"_" + DateTime.Now.ToString("dd-MM-yy_hh:mm:ss") + ".xls");
        string cs = LoginForm.cs;
        date = dtPicker.Value.ToString("yyyy-MM-dd");
        if (cnn.State == ConnectionState.Closed)
        {
            cnn.Open();
        }
        
   
        sqlCmd = new SqlCommand("RptIV030", cnn);
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.Parameters.AddWithValue("@DateFrom", date);
        SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);
        sqlSda.Fill(dtData);
        dGInvHD.DataSource = dtData;
        
        writeExcelFile(location,dtData);
        btnExport.Enabled = true;
        //return dtData;
    }

写入 Excel 函数

public void writeExcelFile(string location, DataTable read)
    {        
        

        //Create excel app object
        Microsoft.Office.Interop.Excel.Application xlSamp = new Microsoft.Office.Interop.Excel.Application();
        if (xlSamp == null)
        {
            MessageBox.Show("Excel is Not Installed");
            //Console.WriteLine("Excel is not Insatalled");
            //Console.ReadKey();
            return;
        }

        //Create a new excel book and sheet
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;


        xlWorkBook = xlSamp.Workbooks.Add(misValue);
        // get the reference of first sheet.
        // store its reference to worksheet 
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet = xlWorkBook.ActiveSheet;
        // changing the name of active sheet
        xlWorkSheet.Name = "Having Dues-" + name + "-Till-" + date;
        // column headings
        for (var i = 0; i < read.Columns.Count; i++)
        {
            xlWorkSheet.Cells[1, i + 1] = read.Columns[i].ColumnName;
        }
        // rows
        for (var i = 0; i < read.Rows.Count; i++)
        {
            // to do: format datetime values before printing
            for (var j = 0; j < read.Columns.Count; j++)
            {
                xlWorkSheet.Cells[i + 2, j + 1] = read.Rows[i][j];
            }
        }


        //Save the opened excel book to custom location
        //Dont forget, you have to add to exist location
        xlWorkBook.SaveAs(location, 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);
        xlSamp.Quit();

        //release Excel Object 
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSamp);
            xlSamp = null;
        }
        catch (Exception ex)
        {
            xlSamp = null;
         
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            GC.Collect();
        }

    }

当我执行我的代码时，我可以在 gridview 中查看数据，但无法将其写入 excel 文件。

我收到错误xlWorkBook.SaveAs(location, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

错误信息

无法访问该文件。尝试以下方法之一：

Make sure the specified folder exists. 
Make sure the folder that contains the file is not read-only.
Make sure the filename and folder path do not contain any of the following characters:  <  >  ?  [  ]  :  | or  *
Make sure the filename and folder path do not contain more than 218 characters.

任何帮助将不胜感激。

标签： c#excelinterop

使用类似这样的东西我认为它很有用：

 xlSamp.ActiveWorkbook.SaveAs(filepath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                        xlSamp.ActiveWorkbook.Close(true, missing, missing);
                        xlSamp.Quit();

确保文件名和文件夹路径不超过 218 个字符。

当您的位置路径将是长减少路径时，会出现此错误

确保指定的文件夹存在。
确保包含该文件的文件夹不是只读的。

确保您的位置文件夹必须存在于该位置并授予对该文件夹的读写权限

确保文件名和文件夹路径不包含以下任何字符：< > ? [ ] : | 或者 *

给文件夹名称和文件名正确的格式

c# - 无法将数据表写入Excel c#

问题描述

解决方案

推荐阅读