我现在要将数据库里的数据导出到excel,然后再将excel导入到数据库,在导入数据库时用的是将excel读取到ds,然后将ds更新到数据库的方法。但现在有个问题是我将导出的excel读取到ds时,读取到的内容是空的,但是如果我把excel文件打开然后再保存一下,就可以正常读取了。请帮忙看看是哪儿的问题,谢谢了。导出excel的代码如下
XlsDocument xls = new XlsDocument(); int rowIndex = 1;
int colIndex = 0; System.Data.DataTable table = ds.Tables[0];
Worksheet sheet = xls.Workbook.Worksheets.AddNamed(xlsName);//状态栏标题名称
Cells cells = sheet.Cells;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
Cell cell = cells.AddValueCell(1, colIndex, col.ColumnName);
cell.Font.Height = 14 * 20;
cell.Font.FontFamily = FontFamilies.Roman;
} foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
Cell cell = cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//转换为数字型 cell.Font.FontFamily = FontFamilies.Default; //字体
cell.Font.Height = 12 * 20;
//cell.Font.Bold = true; //字体为粗体
cell.ShrinkToCell = false;
}
}
xls.FileName = string.Format("{0}.xls", xlsName);
xls.Send();
将excel读取到ds的代码如下
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
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();
XlsDocument xls = new XlsDocument(); int rowIndex = 1;
int colIndex = 0; System.Data.DataTable table = ds.Tables[0];
Worksheet sheet = xls.Workbook.Worksheets.AddNamed(xlsName);//状态栏标题名称
Cells cells = sheet.Cells;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
Cell cell = cells.AddValueCell(1, colIndex, col.ColumnName);
cell.Font.Height = 14 * 20;
cell.Font.FontFamily = FontFamilies.Roman;
} foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
Cell cell = cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//转换为数字型 cell.Font.FontFamily = FontFamilies.Default; //字体
cell.Font.Height = 12 * 20;
//cell.Font.Bold = true; //字体为粗体
cell.ShrinkToCell = false;
}
}
xls.FileName = string.Format("{0}.xls", xlsName);
xls.Send();
将excel读取到ds的代码如下
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
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();
DataSet ds = null;
---------------------------
不要等于空,直接加载试试。 我读这些代码,没有出现过问题啊。
http://blog.csdn.net/small5e4444/archive/2010/07/15/5738212.aspx
Excel.Application excelApplication = null;
Excel.Workbooks excelWorkBooks = null;
Excel.Workbook excelWorkBook = null;
Excel.Worksheet excelWorkSheet = null;
int excelActiveWorkSheetIndex = 1; //活动工作表索引
excelApplication = new Excel.ApplicationClass();//声明并初始化一个Excel应用程序
excelWorkBooks = excelApplication.Workbooks;//得到工作薄集合
//根据path路径打开相应的工作薄
excelWorkBook = excelWorkBooks._Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//得到当前的工作区
excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex];
excelApplication.Visible = false; int columnNumber = 0;//记录总的数据列数
int rowsNumber = 0;//记录总的数据行数 try
{
while (true)
{
if (((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 2]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 3]).Text.ToString().Trim() == "")
break;
columnNumber++;
}
}
catch
{
columnNumber = -1;
}
try
{
while (true)
{
if (((Excel.Range)excelWorkSheet.Cells[rowsNumber + 1, 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[rowsNumber + 2, 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[rowsNumber + 3, 1]).Text.ToString().Trim() == "")
break;
rowsNumber++;
}
}
catch
{
rowsNumber = -1;
} DataSet ds = null; //如果发生异常或者在本地数据表中只有标题,则是错误的,返回空的数据集
if (columnNumber == -1 || rowsNumber == -1 || rowsNumber == 1)
{
return ds;
}
//否则初始化一个数据集,数据表和数据行
ds = new DataSet();
DataTable dt = new DataTable();
DataRow dr = null;
for (int columnID = 1; columnID <= columnNumber; columnID++)//循环设置数据表的标题名称
dt.Columns.Add(((Excel.Range)excelWorkSheet.Cells[1, columnID]).Text.ToString()); for (int rowID = 2; rowID <= rowsNumber; rowID++)//从第二行开始读取实际的数据
{
dr = dt.NewRow();//新行
for (int columnID = 1; columnID <= columnNumber; columnID++)
{
dr[columnID - 1] = ((Excel.Range)excelWorkSheet.Cells[rowID, columnID]).Text.ToString();
//读到空值null和读到空串""分别处理,此处为测试数据,先放着
}
dt.Rows.Add(dr);
} //把改清除的信息清除,不占进程和内存
excelWorkBooks = null;
excelWorkBook = null;
excelWorkSheet = null;
excelApplication.Workbooks.Close();
excelApplication.Quit();
excelApplication = null;
dt.AcceptChanges();
//把数据表添加到数据集中
ds.Tables.Add(dt);
return ds;//返回
这个方法debug是没问题的,但是网站发布后就出问题了,感觉像是对excel操作权限的问题,但始终没有解决,请帮忙看看这个方法也可以啊
-----------------------------------------------------------------------
iis 指定的路径---属性 ---权限为管理(或完全控制)--或那个文件夹的访问权限。
试试
-------------------------------------------
1,你说的debug运行没有问题是不是 本机。而发布后是不是到了服务器上了。
也就是 行和不行是不是 在一个计算机上可以,另一个计算机不可以。
2,9楼你的回复 有什么错误提示吗 , 就单单没有数据吗?
ApplicationClass excel;
_Workbook xBk;
_Worksheet xSt;
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
你可以在你导入的时候抛出异常看下报错不
其实标准操作还是第二种,就用它吧,我做了半年多excel操作了,很少用流去导出
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [" + sheetName + "$" + columnName + ":" + columnName + "] ";//WHERE NAME = '" + txtColumnName + "'";//可是更改Sheet名称,比如sheet2,等等 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, sheetName);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}