using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Reflection;
namespace DataOutAndIn
{
/// <summary>
/// OutPutExcel 导出为EXCEL
/// </summary>
public class OutPutExcel
{
private Excel.Application oXL;
private Excel._Workbook oWB;
private Excel._Worksheet oSheet;
private Excel.Range oRng;
private System.Data.DataTable dt;
private System.Windows.Forms.SaveFileDialog SFD = new SaveFileDialog();
public OutPutExcel()
{
this.dt = new System.Data.DataTable();
}
/// <summary>
/// 快速导出Excel,直接在保存对话框中设好你要保存的路径和名字即可
/// </summary>
/// <param name="dtDataTable">用户指定的数据表</param>
public void FastOutPutExcel(System.Data.DataTable dtDataTable)
{
this.dt = dtDataTable;
try
{
this.SFD.Filter =" Excel文件 (*.xls)|*.xls";
this.SFD.FilterIndex = 1;
this.SFD.FileName = this.dt.TableName.ToString()+".xls";
this.SFD.RestoreDirectory = false;
if(this.SFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
#region///////////////////////////////////2007.05.23从条件外移入/////////////////////////////////////
//启动Excel,并获取应用程序实例
try
{
oXL = new Excel.Application();
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
oXL.Visible = false;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//把表的列名加载进excel的第一行
for(int i = 0;i<this.dt.Columns.Count;i++)
{
oSheet.Cells[1,i+1] = this.dt.Columns[i].ColumnName.ToString();
}
//设置第一行标题的字体格式为黑体居中
// oSheet.get_Range("A1", "D1").Font.Bold = true;
// oSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//
//创建一个二维数组把表中的数据读进来等待进行向excel加载数据的操作
string[,] strCellText = new string[this.dt.Rows.Count,this.dt.Columns.Count];
try
{
for(int i = 0;i < this.dt.Rows.Count;i++)
{
for(int j = 0;j < this.dt.Columns.Count;j++)
{
strCellText[i,j] = this.dt.Rows[i][j].ToString();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
string ExcelColumLable = this.GetCellNameLable((int)(this.dt.Columns.Count/26))+this.GetCellNameLable((int)(this.dt.Columns.Count%26));
string max = ExcelColumLable+"1";
int row = this.dt.Rows.Count +1;
ExcelColumLable += row.ToString();
#endregion///////////////////////////////////////////////////////////////////////////////////////////////
oSheet.get_Range("A2", ExcelColumLable).Value2 = strCellText;
oRng = oSheet.get_Range("A1", max);
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = true;
oSheet.SaveAs(this.SFD.FileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
try
{
oXL.Workbooks.Close();
oXL.Quit();
oXL = null;
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
}
// else
// {
// try
// {
// oXL.Workbooks.Close();
// oXL.Quit();
// oXL = null;
// }
// catch(Exception exp)
// {
// MessageBox.Show(exp.Message+"测试");
// }
// }
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
MessageBox.Show( errorMessage, "Error" );
}
}
/// <summary>
/// 快速导出Excel,直接在保存对话框中设好你要保存的路径和名字即可,最多只导出二十六列以内的表
/// </summary>
/// <param name="dtDataTable">用户指定的数据表</param>
public void FastOutPutExcelIn26Colunms(System.Data.DataTable dtDataTable)
{
this.dt = dtDataTable;
try
{
this.SFD.Filter =" Excel文件 (*.xls)|*.xls";
this.SFD.FilterIndex = 1;
this.SFD.FileName = this.dt.TableName.ToString()+".xls";
this.SFD.RestoreDirectory = false;
if(this.SFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
#region/////////////////////////////////////2007.05.23从条件语句外移入/////////////////////////////////////////////////
//启动Excel并获取应用程序实例
try
{
oXL = new Excel.Application();
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
oXL.Visible = false;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//把表的列名加载进excel的第一行
for(int i = 0;i<this.dt.Columns.Count;i++)
{
oSheet.Cells[1,i+1] = this.dt.Columns[i].ColumnName.ToString();
}
//创建一个二维数组把表中的数据读进来等待进行向excel加载数据的操作
string[,] strCellText = new string[this.dt.Rows.Count,this.dt.Columns.Count];
try
{
for(int i = 0;i < this.dt.Rows.Count;i++)
{
for(int j = 0;j < this.dt.Columns.Count;j++)
{
strCellText[i,j] = this.dt.Rows[i][j].ToString();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
string ExcelColumLable = this.GetCellNameLable((int)(this.dt.Columns.Count%26));
string max = ExcelColumLable+"1";
int row = this.dt.Rows.Count +1;
ExcelColumLable += row.ToString();
#endregion/////////////////////////////////////////////////////////////////////////////////////////
oSheet.get_Range("A2", ExcelColumLable).Value2 = strCellText;
oRng = oSheet.get_Range("A1", max);
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = true;
oSheet.SaveAs(this.SFD.FileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
try
{
oXL.Workbooks.Close();
oXL.Quit();
oXL = null;
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
}
// else
// {
// try
// {
// oXL.Workbooks.Close();
// oXL.Quit();
// oXL = null;
// }
// catch(Exception exp)
// {
// MessageBox.Show(exp.Message);
// }
// }
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
MessageBox.Show( errorMessage, "Error" );
}
}
/// <summary>
/// 逐条导出Excel,它将打开一个Excel表格并把指定的表内数据导出到这个Excel进程中来
/// </summary>
/// <param name="dtDataTable">用户指定的数据表</param>
public void SlowOutPutExcel(System.Data.DataTable dtDataTable)
{
this.dt = dtDataTable;
try
{
//启动EXCEL,并获取应用程序实例
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//把表的列名加载进excel的第一行
for(int i = 0;i<this.dt.Columns.Count;i++)
{
oSheet.Cells[1,i+1] = this.dt.Columns[i].ColumnName.ToString();
}
//设置第一行标题的字体格式为黑体居中
// oSheet.get_Range("A1", "D1").Font.Bold = true;
// oSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
try
{
for(int i = 0;i < this.dt.Rows.Count;i++)
{
for(int j = 0;j < this.dt.Columns.Count;j++)
{
oSheet.Cells[i+2,j+1] = this.dt.Rows[i][j].ToString();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
string ExcelColumLable = this.GetCellNameLable((int)(this.dt.Columns.Count/26))+this.GetCellNameLable((int)(this.dt.Columns.Count%26));
string max = ExcelColumLable+"1";
oRng = oSheet.get_Range("A1", max);
oRng.EntireColumn.AutoFit();
oXL.Visible = true;
oXL.UserControl = true;
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
MessageBox.Show( errorMessage, "Error" );
}
}
private string GetCellNameLable(int index)
{
string lable = "";
switch(index)
{
case 1:
lable = "A";
break;
case 2:
lable = "B";
break;
case 3:
lable = "C";
break;
case 4:
lable = "D";
break;
case 5:
lable = "E";
break;
case 6:
lable = "F";
break;
case 7:
lable = "G";
break;
case 8:
lable = "H";
break;
case 9:
lable = "I";
break;
case 10:
lable = "J";
break;
case 11:
lable = "K";
break;
case 12:
lable = "L";
break;
case 13:
lable = "M";
break;
case 14:
lable = "N";
break;
case 15:
lable = "O";
break;
case 16:
lable = "P";
break;
case 17:
lable = "Q";
break;
case 18:
lable = "R";
break;
case 19:
lable = "S";
break;
case 20:
lable = "T";
break;
case 21:
lable = "U";
break;
case 22:
lable = "V";
break;
case 23:
lable = "W";
break;
case 24:
lable = "X";
break;
case 25:
lable = "Y";
break;
case 0:
lable = "Z";
break;
default :
break;
}
return lable;
}
}
}
-------------------------------------------------------------
本程序在进行10到26进制转换时,做得不好.你可以进行修改使用.
不过,现在这个,在上百列之内是不会有问题的.
你可能在使用时需要引用excel和office.
如果想要导入到数据库中去.一般的思路是先把excel当成一个数据库进行查询,再把查询到的数据写入目标库.这个过程比导出简单多了.
温馨提示:内容为网友见解,仅供参考