格式一:直接导入一个新的excel
/// <summary>
/// 直接导出到指定excel
/// </summary>
/// <param name="topath"></param>
/// <param name="dt"></param>
private void SaveAsExcel(string topath, DataTable dt)
{
object objMissing = System.Reflection.Missing.Value;//缺省默认值
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
Microsoft.Office.Interop.Excel.Range ranges = null;
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Range head = null;
/*创建新excel对象*/
workBook = app.Workbooks.Add(objMissing);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
/*循环设置第一行名称*/
for (int i = 0; i < dt.Columns.Count; i++)
{
head = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, i + 1];
head.Value2 = dt.Columns[i].ColumnName;
head = null;
}
try
{
//A2用于指定起始行的位置
range = workSheet.get_Range("A2", objMissing);
int rows = dt.Rows.Count, cols = dt.Columns.Count;
ranges = range.get_Resize(rows, cols);
object[,] datas = new object[rows, cols];
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
datas[i, j] = dt.Rows[i][j].ToString();
}
}
ranges.Value2 = datas;
workBook.SaveAs(topath, objMissing, objMissing, objMissing, objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, objMissing,
objMissing, objMissing, objMissing, objMissing);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
workBook.Close(false, objMissing, objMissing);
app.Quit();
range = null;
ranges = null;
workBook = null;
workSheet = null;
app = null;
}
}
/// <summary>
/// 根据特定模板,填充数据后,导出到excel
/// </summary>
/// <param name="frompath"></param>
/// <param name="topath"></param>
/// <param name="dt"></param>
private void SaveAsExcel(string frompath, string ExcelName, DataTable dt)
{
object objMissing = System.Reflection.Missing.Value;//缺省值,相当于为空
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
//以下3个对象在前面定义,为了能在异常中可以释放
Microsoft.Office.Interop.Excel.Range ranges = null;
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Range head = null;
/*读取现有excel*/
workBook = app.Workbooks.Add(frompath);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
head = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1,1];
head.Value2 = ExcelName;//文件及标题名称
head = null;
try
{
//A4用于指定起始行的位置(根据自己的需要设置)
range = workSheet.get_Range("A4", objMissing);
int rows = dt.Rows.Count, cols = dt.Columns.Count;
ranges = range.get_Resize(rows, cols);
object[,] datas = new object[rows, cols];
for (int i = 0; i < rows; i++)
{
for (int j = 2; j < cols - 3; j++)
{
datas[i, j] = dt.Rows[i][j].ToString();
}
}
ranges.Value2 = datas;
string path = @"C:\Documents and Settings\Administrator\桌面\" + ExcelName + ".xls";
workBook.SaveAs(path, objMissing, objMissing, objMissing, objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, objMissing,
objMissing, objMissing, objMissing, objMissing);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
workBook.Close(false, objMissing, objMissing);
app.Quit();
range = null;
ranges = null;
workBook = null;
workSheet = null;
app = null;
}
}