代码如下。
public bool Exprot(DataSet ds, string filePath)
{
if (ds == null && ds.Tables.Count == 0) return false; //没有数据表。直接返回
if (string.IsNullOrEmpty(filePath)) return false; //路径为空 bool result = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value; if (xlApp == null) throw new Exception("无法创建Excel对象,可能您的电脑没有安装Excel!"); try
{
Microsoft.Office.Interop.Excel.Workbooks workBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //-----------下面循环写入单个表
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
this.ExprotSingleTable(ds.Tables[0], ref workSheet);
if (ds.Tables.Count > 1)
{
workBook.Worksheets.Add(workSheet, missing, ds.Tables.Count - 1, Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
for (int i = 1; i < ds.Tables.Count; i++)
{
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i+1];
this.ExprotSingleTable(ds.Tables[i], ref workSheet);
}
}
//-----------上面循环写入单个表 //保存Excel文件
workBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
workBook.Close(true, missing, missing);
workBooks.Close();
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
xlApp.Quit();
}
return result;
}在
workBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); 这一行这里就报异常:异常来自 HRESULT:0x800A03EC
各位高手救救命吧。弄好两三天了。查了N多的资料了。还是搞不定。
public bool Exprot(DataSet ds, string filePath)
{
if (ds == null && ds.Tables.Count == 0) return false; //没有数据表。直接返回
if (string.IsNullOrEmpty(filePath)) return false; //路径为空 bool result = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value; if (xlApp == null) throw new Exception("无法创建Excel对象,可能您的电脑没有安装Excel!"); try
{
Microsoft.Office.Interop.Excel.Workbooks workBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //-----------下面循环写入单个表
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
this.ExprotSingleTable(ds.Tables[0], ref workSheet);
if (ds.Tables.Count > 1)
{
workBook.Worksheets.Add(workSheet, missing, ds.Tables.Count - 1, Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
for (int i = 1; i < ds.Tables.Count; i++)
{
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i+1];
this.ExprotSingleTable(ds.Tables[i], ref workSheet);
}
}
//-----------上面循环写入单个表 //保存Excel文件
workBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
workBook.Close(true, missing, missing);
workBooks.Close();
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
xlApp.Quit();
}
return result;
}在
workBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); 这一行这里就报异常:异常来自 HRESULT:0x800A03EC
各位高手救救命吧。弄好两三天了。查了N多的资料了。还是搞不定。
workbook.SaveAs(FileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
{
bool result = false;
try
{
Microsoft.Office.Interop.Excel.Range range; long totalCount = dt.Rows.Count;
long rowRead = 0;
long percent = 0; workSheet.Cells[1, 1] = dt.TableName; //写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
workSheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
} //写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
workSheet.Cells[r + 3, c + 1] = dt.Rows[r][c];
}
rowRead++;
percent = (100 * rowRead) / totalCount;
if (ShowExprotPercent != null) ShowExprotPercent(percent);
}
/*
range = workSheet.get_Range(workSheet.Cells[2, 1], workSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
null);
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 0)
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
*/
result = true;
}
catch (Exception ex)
{
yjdbunity yj = new yjdbunity();
yj.SaveLog(this.GetType().Name, "ExprotSingleTable", ex.ToString());
}
return result;
}
读写Excel用这种方法太慢了,用OLEDB Provider比较快