在Windows窗体应用程序中将DataGridView转换为Excel抛出异常 [英] Converting DataGridView to Excel throwing exception in windows forms application

问题描述

我正在尝试使用Microsoft interop dll将gridview导出到excel。以下是我从某些来源获得的代码。此代码使用默认名称在我的文档文件夹中创建excel文件。但是当我尝试再次单击convert to excel按钮时，它会显示默认提示是否覆盖现有的。如果我说不，那么它会抛出以下异常。此外，它并没有促使我提及我自己的文件名并选择要保存的路径。

请帮助我......

这是我得到的例外。抛出此异常的saveas方法。

来自HRESULT的异常：0x800A03EC 
 System.Runtime.InteropServices.COMException未处理
消息=来自HRESULT的异常：0x800A03EC 
 Source = AssetManagementSystem 
 ErrorCode = -2146827284 

以下是代码：

  private   void  btnExportExcel_Click（ object  sender，EventArgs e）
 {
 Microsoft.Office.Interop.Excel.Application excel =  null ; 
 Microsoft.Office.Interop.Excel.Workbook wb =  null ; 
 
  object  misValue = System.Reflection.Missing.Value; 
 
 System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; 
 System.Threading.Thread.CurrentThread.CurrentCulture =  new  System.Globalization.CultureInfo（  en-US）; 
 
 Microsoft.Office.Interop.Excel.Application ExcelApp =  new  Microsoft.Office.Interop.Excel.Application（）; 
 ExcelApp.Application.Workbooks.Add（Type.Missing）; 
 字符串 st = System.IO.Directory.GetCurrentDirectory（）+   \\A.xlsx; 
  //  更改工作簿的属性 
 ExcelApp.Columns.ColumnWidth = < span class =code-digit> 20 ; 
 
  //  在Excel中存储标题部分 
  for （ int  i =  1 ; i <  gvContractDetails.Columns.Count +  1 ; i ++）
 {
 ExcelApp.Cells [  1 ，i] = gvContractDetails.Columns [i  -   1 ]。HeaderText; 
} 
 
  //  将每行和每列值存储到excel表 
  for （ int  i =  0 ; i <  gvContractDetails.Rows.Count  -   1 ; i ++）
 {
  for （ int  j =  0 ; j <  gvContractDetails.Columns.Count; j ++）
 {
 ExcelApp.Cells [i +  2 ，j +  1 ] = gvContractDetails.Rows [i] .Cells [j] .Value.ToString（）; 
} 
} 
 ExcelApp.Visible =  true ; 
 ExcelApp.ActiveWorkbook.SaveAs（Type.Missing，Type.Missing，Type.Missing，Type.Missing，
 Type.Missing，Type.Missing，Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive， 
 Type.Missing，Type.Missing，Type.Missing，Type.Missing）; 
 ExcelApp.ActiveWorkbook.Saved =  true ; 
 ExcelApp.Quit（）; 
 System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; 
} 

解决方案

我看到你创建了一个文件名

 字符串 st = System.IO.Directory.GetCurrentDirectory（）+   \\A.xlsx; 

但是我找不到你在哪里使用这个变量？



请确保你在保存你的Excel时传递正确的参数。

< blockquote>我现在得到了解决方案。如果此代码有任何问题，请告诉我们：

 public static void ExportGridViewToExcel（DataGridView gridview）
 {
 try 
 {
 System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; 
 System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo（en-US）; 
 //Microsoft.Office.Interop.Excel.Application excel = null; 
 Microsoft.Office.Interop.Excel.Workbook wb = null; 
 Microsoft.Office.Interop.Excel._Worksheet wSheet = null; 
 object misValue = System.Reflection.Missing.Value; 
 
 Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application（）; 
 wb = ExcelApp.Workbooks.Add（Missing.Value）; 
 wSheet = wb.Sheets.Add（Type.Missing）; 
 // wSheet = wb.Sheets [sheet1]; 
 ExcelApp.Columns.ColumnWidth = 20; 
 ExcelApp.Visible = false; 
 //在Excel 
中存储标题部分（int i = 1; i< gridview.Columns.Count + 1; i ++）
 {
 wSheet.Cells [1， i] = gridview.Columns [i  -  1] .HeaderText; 
} 
 //将每行和每列的值存储到excel表
 for（int i = 0; i< gridview.Rows.Count  -  1; i ++）
 {
 for（int j = 0; j< gridview.Columns.Count; j ++）
 {
 wSheet.Cells [i + 2，j + 1] = gridview.Rows [i]。细胞[j]的.Value.ToString（）; 
} 
} 
 ExcelApp.Visible = true; 
 string fileName = SaveAs（）; 
 wSheet.SaveAs（fileName）; 
 wb.Saved = true; 
 wb.Close（）; 
 ExcelApp.Quit（）; 
 System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; 
 ExcelDocViewer（fileName）; 
} 
 catch（Exception ex）
 {
} 
} 

 public static void ExcelDocViewer（string fileName）
 {
 try 
 {
 System.Diagnostics.Process.Start（fileName）; 
} 
 catch {} 
} 
 

 public static string SaveAs（）
 {
 string FileName = null; 
 SaveFileDialog saveFileDialog = new SaveFileDialog（）; 
 saveFileDialog.InitialDirectory = Environment.GetFolderPath 
（Environment.SpecialFolder.Personal）; 
 saveFileDialog.Filter =Excel Files（* .xlsx）| * .xlsx; 
 if（saveFileDialog.ShowDialog（）== DialogResult.OK）
 {
 FileName = saveFileDialog.FileName; 
} 
返回FileName; 
} 
 

Hi,

I am trying to export the gridview to excel using Microsoft interop dll. Below is the code that I got from some source. This code is creating excel file in my documents folder with a default name. But when I try to click the convert to excel button again, then it is showing a default prompt whether to overwrite the existing or not. If I say No, then it's throwing the below exception. Moreover, it's not prompting me to mention my own file name and select the path to save.
Please help me...
This is the exception I am getting. The saveas method throwing this exception.

Exception from HRESULT: 0x800A03EC
System.Runtime.InteropServices.COMException was unhandled
  Message=Exception from HRESULT: 0x800A03EC
  Source=AssetManagementSystem
  ErrorCode=-2146827284

Here is the code:

private void btnExportExcel_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;
            
            object misValue = System.Reflection.Missing.Value;
            
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            String st = System.IO.Directory.GetCurrentDirectory() + "\\A.xlsx";
            // Change properties of the Workbook 
            ExcelApp.Columns.ColumnWidth = 20;

            // Storing header part in Excel
            for (int i = 1; i < gvContractDetails.Columns.Count + 1; i++)
            {
                ExcelApp.Cells[1, i] = gvContractDetails.Columns[i - 1].HeaderText;
            }

            // Storing Each row and column value to excel sheet
            for (int i = 0; i < gvContractDetails.Rows.Count - 1; i++)
            {
                for (int j = 0; j < gvContractDetails.Columns.Count; j++)
                {
                    ExcelApp.Cells[i + 2, j + 1] = gvContractDetails.Rows[i].Cells[j].Value.ToString();
                }
            }
            ExcelApp.Visible = true;
            ExcelApp.ActiveWorkbook.SaveAs(Type.Missing,Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
        }

解决方案

I see that you created a file name

String st = System.IO.Directory.GetCurrentDirectory() + "\\A.xlsx";

However I couldn't find where have you used this variable?

Please make sure you are passing correct arguments while saving your excel.

I got the solution now. Please let me know if there are any issues with this code:

public static void ExportGridViewToExcel(DataGridView gridview)
        {
            try
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                //Microsoft.Office.Interop.Excel.Application excel = null;
                Microsoft.Office.Interop.Excel.Workbook wb = null;
                Microsoft.Office.Interop.Excel._Worksheet wSheet = null;
                object misValue = System.Reflection.Missing.Value;
                
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                wb = ExcelApp.Workbooks.Add(Missing.Value);
                wSheet = wb.Sheets.Add(Type.Missing);
                //wSheet = wb.Sheets["sheet1"];
                ExcelApp.Columns.ColumnWidth = 20;
                ExcelApp.Visible = false;
                // Storing header part in Excel
                for (int i = 1; i < gridview.Columns.Count + 1; i++)
                {
                    wSheet.Cells[1, i] = gridview.Columns[i - 1].HeaderText;
                }
                // Storing Each row and column value to excel sheet
                for (int i = 0; i < gridview.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < gridview.Columns.Count; j++)
                    {
                        wSheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Value.ToString();
                    }
                }
                ExcelApp.Visible = true;
                string fileName = SaveAs();
                wSheet.SaveAs(fileName);
                wb.Saved = true;
                wb.Close();
                ExcelApp.Quit();
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                ExcelDocViewer(fileName);
            }
            catch (Exception ex)
            {
            }
        }

public static void ExcelDocViewer(string fileName)
{
    try
    {
        System.Diagnostics.Process.Start(fileName);
    }
    catch { }
}

public static string SaveAs()
{
    string FileName = null;
    SaveFileDialog saveFileDialog = new SaveFileDialog();
    saveFileDialog.InitialDirectory = Environment.GetFolderPath
    (Environment.SpecialFolder.Personal);
    saveFileDialog.Filter = "Excel Files (*.xlsx)|*.xlsx";
    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        FileName = saveFileDialog.FileName;
    }
    return FileName;
}

这篇关于在Windows窗体应用程序中将DataGridView转换为Excel抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！