解决方案 »
- Socket.Blocking=true会阻塞应用程序主线程吗?
- sql执行insert语句后没有报错,数据却没有插入?
- 大家说说大项目和小项目具体有什么差别呀(请从技术角度谈谈)
- C#,ASP.NET 画面上显示数据的实现方法
- c#中,如何设定数组,使得动态加载的控件中的值,可更新到数据库
- 项目调试中的奇怪问题,向各位高手请教!有可行方法立即结帖给全分!!!
- MVC3页面跳转的问题
- 问一个面试题目,主要是考对浮点数的精确运算,不太会,麻烦教教
- DataSet的简单问题
- 如何作一个象Office一样的取色框?
- DevExpress BarManager快捷键无法使用
- 同样的代码怎么在两台电脑上运行,其中一台报错了呢?????
看是到哪步出现的问题啊
没找到问题在哪儿,row.add直接添加数据不知道为什么会出这样的问题
看是到哪步出现的问题啊
没找到问题在哪儿,row.add直接添加数据不知道为什么会出这样的问题
调试不是只看最后table的值,你可以看j的值,i的值,添加一列后table的值,都是可以观察到的
看是到哪步出现的问题啊
没找到问题在哪儿,row.add直接添加数据不知道为什么会出这样的问题
调试不是只看最后table的值,你可以看j的值,i的值,添加一列后table的值,都是可以观察到的
i j的值都没问题,只是table.DefaultView.Count 一直在累加,估计跟这个有关系。
,不知道如何重置这个值。。
添加列和行分开来,不要放在同一个for里面
添加列和行分开来,不要放在同一个for里面
已经自行解决。谢了。
不知道你为什么要这么写
方法一
/// <summary>
/// 解析Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
方法二
/// <summary>
/// 解析Excel,返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
string bookName = ws.Name;
for (int i = 1; i < rows + 1; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= columns; j++)
{
_Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
if (i == 1)
dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:"+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
方法三 用NPOI public static DataTable ImportExcel(string filePath)
{
try
{ //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HSSFWorkbook workbook = new HSSFWorkbook(file);
//获取excel的第一个sheet
HSSFSheet sheet = workbook.GetSheetAt(0);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = sheet.GetRow(0);
if(headerRow==null)
headerRow = sheet.GetRow(1);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
HSSFCell cell = headerRow.GetCell(i);
if (cell != null)
{
DataColumn column = new DataColumn(cell.StringCellValue);
table.Columns.Add(column);
}
}
//最后一列的标号 即总的行数
// int rowCount = sheet.LastRowNum;
cellCount = table.Columns.Count;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null)
continue;
DataRow dataRow = table.NewRow();
bool isAdd = false;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) == null)
continue;
row.GetCell(j).SetCellType(HSSFCellType.STRING);
if (row.GetCell(j) != null && row.GetCell(j).StringCellValue.Trim() != "")
{
dataRow[j] = row.GetCell(j).StringCellValue;
isAdd = true;
}
}
if (isAdd)
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return table;
}
catch (Exception ex)
{
LogInfo.Log(ex);
throw ex;
return null;
}
}
参考:http://blog.csdn.net/happy09li/article/details/7431967
不知道你为什么要这么写
方法一
/// <summary>
/// 解析Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
方法二
/// <summary>
/// 解析Excel,返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
string bookName = ws.Name;
for (int i = 1; i < rows + 1; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= columns; j++)
{
_Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
if (i == 1)
dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:"+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
方法三 用NPOI public static DataTable ImportExcel(string filePath)
{
try
{ //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HSSFWorkbook workbook = new HSSFWorkbook(file);
//获取excel的第一个sheet
HSSFSheet sheet = workbook.GetSheetAt(0);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = sheet.GetRow(0);
if(headerRow==null)
headerRow = sheet.GetRow(1);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
HSSFCell cell = headerRow.GetCell(i);
if (cell != null)
{
DataColumn column = new DataColumn(cell.StringCellValue);
table.Columns.Add(column);
}
}
//最后一列的标号 即总的行数
// int rowCount = sheet.LastRowNum;
cellCount = table.Columns.Count;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null)
continue;
DataRow dataRow = table.NewRow();
bool isAdd = false;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) == null)
continue;
row.GetCell(j).SetCellType(HSSFCellType.STRING);
if (row.GetCell(j) != null && row.GetCell(j).StringCellValue.Trim() != "")
{
dataRow[j] = row.GetCell(j).StringCellValue;
isAdd = true;
}
}
if (isAdd)
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return table;
}
catch (Exception ex)
{
LogInfo.Log(ex);
throw ex;
return null;
}
}
参考:http://blog.csdn.net/happy09li/article/details/7431967
MSDN给的示例然后改的。。
不知道你为什么要这么写
方法一
/// <summary>
/// 解析Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
方法二
/// <summary>
/// 解析Excel,返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
string bookName = ws.Name;
for (int i = 1; i < rows + 1; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= columns; j++)
{
_Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
if (i == 1)
dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:"+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
方法三 用NPOI public static DataTable ImportExcel(string filePath)
{
try
{ //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HSSFWorkbook workbook = new HSSFWorkbook(file);
//获取excel的第一个sheet
HSSFSheet sheet = workbook.GetSheetAt(0);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = sheet.GetRow(0);
if(headerRow==null)
headerRow = sheet.GetRow(1);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
HSSFCell cell = headerRow.GetCell(i);
if (cell != null)
{
DataColumn column = new DataColumn(cell.StringCellValue);
table.Columns.Add(column);
}
}
//最后一列的标号 即总的行数
// int rowCount = sheet.LastRowNum;
cellCount = table.Columns.Count;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null)
continue;
DataRow dataRow = table.NewRow();
bool isAdd = false;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) == null)
continue;
row.GetCell(j).SetCellType(HSSFCellType.STRING);
if (row.GetCell(j) != null && row.GetCell(j).StringCellValue.Trim() != "")
{
dataRow[j] = row.GetCell(j).StringCellValue;
isAdd = true;
}
}
if (isAdd)
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return table;
}
catch (Exception ex)
{
LogInfo.Log(ex);
throw ex;
return null;
}
}
参考:http://blog.csdn.net/happy09li/article/details/7431967
MSDN给的示例然后改的。。
#region excel处理 Type type;
type = Type.GetTypeFromProgID("ET.Application");//V8版本类型
if (type == null)//没有安装V8版本
{
type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型
if (type == null)//没有安装V9版本
{
type = Type.GetTypeFromProgID("EXCEL.Application");//MS EXCEL类型
if (type == null)
{
MessageBox.Show("没有安装Office软件");
return;//没有安装Office软件
}
}
}
dynamic app = Activator.CreateInstance(type);//根据类型创建App实例
app.Visible = false;//后台打开,不显示Excel界面
dynamic workbook = app.Workbooks.Open(OFD_1.FileName);//打开aaa.xls文件
dynamic worksheet = workbook.Worksheets[1];//获取Sheet1工作薄 //创建DateTable
System.Data.DataTable table = new System.Data.DataTable("table"); int rows = worksheet.UsedRange.Rows.Count;
int columns = worksheet.UsedRange.Columns.Count;
string bookName = worksheet.Name; for (int i = 1; i < rows + 1; i++)
{
DataRow row = table.NewRow();
for (int j = 1; j <= columns; j++)
{ dynamic range = worksheet.Range[app.Cells[i + 1, j], app.Cells[i + 1, j]];
range.Select();
if (i == 1)
{
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = worksheet.Cells[i, j].Value2; table.Columns.Add(column);//可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
row[j - 1] = app.ActiveCell.Text.ToString();
}
}
table.Rows.Add(row);
} dataGridView1.DataSource = table; #endregion稍微改了下你的代码,运行结果是这样只有第一行,下面的数据都是空的。
不知道你为什么要这么写
方法一
/// <summary>
/// 解析Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
方法二
/// <summary>
/// 解析Excel,返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
string bookName = ws.Name;
for (int i = 1; i < rows + 1; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= columns; j++)
{
_Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
if (i == 1)
dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:"+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
方法三 用NPOI public static DataTable ImportExcel(string filePath)
{
try
{ //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HSSFWorkbook workbook = new HSSFWorkbook(file);
//获取excel的第一个sheet
HSSFSheet sheet = workbook.GetSheetAt(0);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = sheet.GetRow(0);
if(headerRow==null)
headerRow = sheet.GetRow(1);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
HSSFCell cell = headerRow.GetCell(i);
if (cell != null)
{
DataColumn column = new DataColumn(cell.StringCellValue);
table.Columns.Add(column);
}
}
//最后一列的标号 即总的行数
// int rowCount = sheet.LastRowNum;
cellCount = table.Columns.Count;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null)
continue;
DataRow dataRow = table.NewRow();
bool isAdd = false;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) == null)
continue;
row.GetCell(j).SetCellType(HSSFCellType.STRING);
if (row.GetCell(j) != null && row.GetCell(j).StringCellValue.Trim() != "")
{
dataRow[j] = row.GetCell(j).StringCellValue;
isAdd = true;
}
}
if (isAdd)
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return table;
}
catch (Exception ex)
{
LogInfo.Log(ex);
throw ex;
return null;
}
}
参考:http://blog.csdn.net/happy09li/article/details/7431967
MSDN给的示例然后改的。。
#region excel处理 Type type;
type = Type.GetTypeFromProgID("ET.Application");//V8版本类型
if (type == null)//没有安装V8版本
{
type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型
if (type == null)//没有安装V9版本
{
type = Type.GetTypeFromProgID("EXCEL.Application");//MS EXCEL类型
if (type == null)
{
MessageBox.Show("没有安装Office软件");
return;//没有安装Office软件
}
}
}
dynamic app = Activator.CreateInstance(type);//根据类型创建App实例
app.Visible = false;//后台打开,不显示Excel界面
dynamic workbook = app.Workbooks.Open(OFD_1.FileName);//打开aaa.xls文件
dynamic worksheet = workbook.Worksheets[1];//获取Sheet1工作薄 //创建DateTable
System.Data.DataTable table = new System.Data.DataTable("table"); int rows = worksheet.UsedRange.Rows.Count;
int columns = worksheet.UsedRange.Columns.Count;
string bookName = worksheet.Name; for (int i = 1; i < rows + 1; i++)
{
DataRow row = table.NewRow();
for (int j = 1; j <= columns; j++)
{ dynamic range = worksheet.Range[app.Cells[i + 1, j], app.Cells[i + 1, j]];
range.Select();
if (i == 1)
{
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = worksheet.Cells[i, j].Value2; table.Columns.Add(column);//可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
row[j - 1] = app.ActiveCell.Text.ToString();
}
}
table.Rows.Add(row);
} dataGridView1.DataSource = table; #endregion稍微改了下你的代码,运行结果是这样只有第一行,下面的数据都是空的。
大神,这是新帖地址,求解答非常感谢!
http://bbs.csdn.net/topics/390912494