使用.xlsx扩展名导出Excel文件 [英] Export Excel File with .xlsx Extension

问题描述

  DataTable dataTable = new DataTable（tableName）; 
 OleDbDataAdapter adapter = new OleDbDataAdapter（select，accessConnection）; 
 adapter.FillSchema（dataTable，SchemaType.Source）; 
 
 foreach（DataRow result in dtResult.Rows）
 {
 DataRow newRow = dataTable.NewRow（）; 
 foreach（DataRow drAssign in dtAssignment.Rows）
 {
 newRow [drAssign [Destination]。ToString（）] = result [drAssign [Source]。ToString（）]; 
} 
 dataTable.Rows.Add（newRow）; 
} 
 
 adapter.Update（dataTable）; 
  

连接字符串是

  Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\Users\AA\Desktop\work10.xlsx;扩展属性=Excel 12.0 Xml; HDR = YES; 
  

我试图将100行导出到xlsx文件。当我尝试打开excel文件时，我将获得

'Excel无法打开test.xlsx，因为文件格式或文件
扩展名无效。验证文件未损坏
，并且文件扩展名与文件'

错误的格式相匹配。 / p>

将.xlsx扩展名更改为.xls后，文件正在打开。

但是xlsx选项必须在不改变的情况下工作

Microsoft Access数据库引擎2010版本和Office Professional Plus 2013安装在计算机上。

我可以解决这个问题吗？

解决方案

这是因为OLEDB作为二进制文件保存，而不是XML，根据 https://support.microsoft.com/en-us/kb/928979

切换到一个XML库，如ClosedXml或使用标准Excel.Interop。

I' m trying to export xlsx file with these codes:

DataTable dataTable= new DataTable(tableName);
OleDbDataAdapter adapter = new OleDbDataAdapter(select, accessConnection);    
adapter.FillSchema(dataTable, SchemaType.Source);

foreach (DataRow result in dtResult.Rows)
{
     DataRow newRow = dataTable.NewRow();
     foreach (DataRow drAssign in dtAssignment.Rows)
     {
          newRow[drAssign["Destination"].ToString()] = result[drAssign["Source"].ToString()];
     }
     dataTable.Rows.Add(newRow);
}

adapter.Update(dataTable);

The connection string is

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AA\Desktop\work10.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

I' m trying to export 100 rows to xlsx file. When I try to open the excel file, I' m getting

'Excel can not open the test.xlsx because file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file'

error.

After changing .xlsx extension to .xls, file is opening.

But xlsx option must be worked without changing extension.

Microsoft Access Database Engine 2010 version and Office Professional Plus 2013 is installed at the computer.

How can I solve this problem?

解决方案

It's because OLEDB is saving as a binary file, not XML, as per https://support.microsoft.com/en-us/kb/928979

Switch to an XML library to do it like ClosedXml or use the standard Excel.Interop.

这篇关于使用.xlsx扩展名导出Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！