110,547
社区成员
发帖
与我相关
我的任务
分享c#导入excel 问题
描述：我用vs2010 开发的winform 程序，导入excel 时报错“未在本地计算机上注册 “microsoft.ace.oledb.12.0 ”提供程序”，我安装的office 是2010 x64位的office 就报错，也安装了AccessDatabaseEngine_X64.exe 这个程序还是报错，目标生成平台也改成x86也不行，哪位大神有成熟的解决的方案，这个问题已经困扰我好几天了，
...全文
请发表友善的回复…
发表回复
青菜萝卜小可爱 2016-03-31
- 打赏
- 举报
把你的启动项属性里，目标平台改成X86
raaaazer 2014-05-07
- 打赏
- 举报
与楼主环境一样 64位系统 用起来没什么问题 打开的是97-2003Excel
这是我的connString
Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=""Excel 8.0;IMEX=1;
於黾 2014-05-07
- 打赏
- 举报
你引用的dll是从哪里来的?
sm695800 2014-05-07
- 打赏
- 举报
我装office 2010 x32位 是可以的，但是装了office 2010 x64位的就不行
by_封爱 版主 2014-05-07
- 打赏
- 举报
NPOI+1
正好在做...
分享下..
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
public class ExcelHelper
{
public class x2003
{
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLS(DataTable dt, string file)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
}
public class x2007
{
#region Excel2007
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
}
public static DataTable GetDataTable(string filepath)
{
var dt = new DataTable("xls");
if (filepath.Last()=='s')
{
dt = x2003.ExcelToTableForXLS(filepath);
}
else
{
dt = x2007.ExcelToTableForXLSX(filepath);
}
return dt;
}
}
var path = Server.MapPath("~/upload/dx.xls");
var dt = ExcelHelper.GetDataTable(path);
g1.DataSource = dt;
g1.DataBind();
kkgoose 2014-05-07
- 打赏
- 举报
以前也遇到过，AccessDatabaseEngine有很多版本，建议尝试卸载安装其它版本
灬浪子灬 2014-05-07
- 打赏
- 举报
用NPOI去实现吧 网上随便搜下，demo应该很多
wind_cloud2011 2014-05-07
- 打赏
- 举报
/// Excel数据导入方法
public void EcxelToDataGridView(string filePath,DataGridView dgv)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";//HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
//导出到execl
public void DataGridViewtoExcel(DataGridView dataGridView1)
{
try
{
if (dataGridView1.Rows.Count == 0)
return;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.Application.Workbooks.Add(true);
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.Application.Workbooks.Add(true).Save();
excel.Save("test.xls");
excel.Quit();
excel = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
wind_cloud2011 2014-05-07
- 打赏
- 举报
那应该与你的office 有关，你安装office 2007 32位的看一下
enaking 2014-05-07
- 打赏
- 举报
缷载office重装就可以了
sm695800 2014-05-07
- 打赏
- 举报
我现在只导入，导出的没写
已经引用了 using System.Data.OleDb;
辣椒小鱼 2014-05-07
- 打赏
- 举报
哥们能不能再附上导出？
wind_cloud2011 2014-05-07
- 打赏
- 举报
引入using System.Data.OleDb;
就可用
sm695800 2014-05-07
- 打赏
- 举报
这个不行，我试过了，在线等啊，，，，
Anymore 2014-05-07
- 打赏
- 举报
try
http://blog.csdn.net/zhaoqiliang527/article/details/4512870
sm695800 2014-05-07
- 打赏
- 举报
//附上代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using System.IO;
namespace 导入Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
var openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel 97-2003 工作簿|*.xls*|Excel 工作簿|*.xlsx|所有文件|*.*";
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
var _file = new FileInfo(openFileDialog.FileName);
DataSet _ds = new DataSet();
DataSet ds = null;
string connStr = null;
Microsoft.Win32.RegistryKey regKey = null;
Microsoft.Win32.RegistryKey regSubKey_2003 = null;
Microsoft.Win32.RegistryKey regSubKey_2007 = null;
//Microsoft.Win32.RegistryKey regSubKey_2013 = null;
regKey = Microsoft.Win32.Registry.LocalMachine;
if (_file.FullName.Substring(_file.FullName.LastIndexOf(".")).ToUpper() == ".XLSX")
{
//connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + _file.FullName + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=No\"";
connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source = " + _file.FullName + ";Extended Properties = 'Excel 12.0;HDR=YES;IMEX=1';";
ds = ImportExcelOleDb(connStr);
}
else
{
regSubKey_2003 = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Common\InstallRoot", false);
regSubKey_2007 = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Common\InstallRoot", false);
if (regSubKey_2007 != null)
{
connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source = " + _file.FullName + ";Extended Properties = 'Excel 12.0;HDR=YES;IMEX=1;'";
ds = ImportExcelOleDb(connStr);
}
else if (regSubKey_2003 != null)
{
connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + _file.FullName + ";Extended Properties=Excel 8.0";
ds = ImportExcelOleDb(connStr);
}
else
{
ds = null;
}
}
dataGridView1.DataSource = ds.Tables[0];
//return ds;
}
}
private static DataSet ImportExcelOleDb(string connStr)
{
//把EXCEL导入到DataSet
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connStr))
{
ArrayList al = new ArrayList();
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow item in sheetNames.Rows)
{
al.Add(item[2]); //itme[2]是获取Excel表单薄名称
}
int n = al.Count;
string[] SheetSet = new string[n];
for (int i = 0; i < n; i++)
{
SheetSet[i] = (string)al[i];
}
OleDbDataAdapter da;
for (int i = 0; i < n; i++)
{
string sql = "select * from [" + SheetSet[i] + "] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[i]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return ds;
}
}
}
