初学C#和面向对象编程,写了个DataTableToExcel的类,请各位朋友给予建议和指导小弟不胜感激代码如下:
(调试通过)
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataTabToExcel
{
//使用前请先：在操作Excel文档之前，需要添加对Excel对象库的引用：引用—COM—Microsoft Excel 11.0 Object Library（版本号可能不同）
interface IDataTabToExcel
{
int CaptionColor { get; set; }
int ContentColor { get; set; }
System.Data.DataTable DataTab { get; set; }
int FinalityColor { get; set; }
bool Format { get; set; }
string MyFather { get; }
event DataTabToExcel.Error OutError;
void OutputExcel();
bool SelfAdapting { get; set; }
event DataTabToExcel.Progress StripProgress;
string TabName { get; set; }
}
public class DataTabToExcel
{
private string Father;
private string tabname;
private DataTable datatab;
private bool format;
private bool selfadapting;
private int captionColor;
private int contentColor;
private int finalityColor;
public delegate void Progress(object sender, EventArgs e);
public delegate void Error(object sender, EventArgs e, string ErrorMessage);
/// <summary>
/// 本过程将在整个导出过程中被激活１００次,可以用来改变进度条等
/// </summary>
public event Progress StripProgress;
/// <summary>
/// 导出Excel表时发生错误时触发
/// </summary>
public event Error OutError;
/// <summary>
/// TabName是导出后表的标题，DataTab是数据源
/// </summary>
/// <remarks>本类将会将DataTable类中的内容导出成Excel表</remarks>
public DataTabToExcel(DataTable DataTab,string TabName)
{
Father = "王磊";
tabname = TabName;
datatab = DataTab;
selfadapting = true;
format = true;
captionColor = 0;
contentColor = 0;
finalityColor = 0;
}
/// <summary>
/// DataTab是数据源
/// </summary>
/// <remarks>本类将会将DataTable类中的内容导出成Excel表</remarks>
public DataTabToExcel(DataTable DataTab)
{
Father = "王磊";
tabname = "无名表";
datatab = DataTab;
selfadapting = true;
format = true;
captionColor = 0;
contentColor = 0;
finalityColor = 0;
}
/// <summary>
/// TabName是导出后表的标题，DataTab是数据源，Format是否格式化单元格
/// </summary>
/// <remarks>本类将会将DataTable类中的内容导出成Excel表</remarks>
public DataTabToExcel(DataTable DataTab, string TabName, bool SelfAdapting)
{
Father = "王磊";
tabname = TabName;
datatab = DataTab;
format = true;
selfadapting = SelfAdapting;
captionColor = 0;
contentColor = 0;
finalityColor = 0;
}
/// <summary>
/// 作者信息
/// </summary>
public string MyFather
{
get
{
return Father;
}
}
/// <summary>
/// 数据源（DataTable），表行数１０万行以下
/// </summary>
public DataTable DataTab
{
get
{
return datatab;
}
set
{
if (value.Rows.Count<100000)
datatab = value;
else
throw (new ArgumentOutOfRangeException("数据源", value, "DataTable中的行超过了１０万，这会用非常长的时间，不建议您使用我"));
}
}
/// <summary>
/// 导出后的Excel表标题，３０个汉字之内
/// </summary>
public string TabName
{
get
{
return tabname;
}
set
{
if (value.Length<30)
tabname = value;
else
throw (new ArgumentOutOfRangeException("标题",value,"超过了３０个字符"));
}
}
/// <summary>
/// 是否给内容打格
/// </summary>
/// <remarks>是否给内容打格</remarks>
public bool Format
{
get
{
return format;
}
set
{
format = value;
}
}
/// <summary>
/// 单元格自动适应内容大小
/// </summary>
/// <remarks>单元格自动适应内容大小</remarks>
public bool SelfAdapting
{
get
{
return selfadapting;
}
set
{
selfadapting = value;
}
}
/// <summary>
/// 标题颜色
/// </summary>
/// <remarks>标题颜色</remarks>
public int CaptionColor
{
get
{
return captionColor;
}
set
{
captionColor = value;
}
}
/// <summary>
/// 内容颜色
/// </summary>
/// <remarks>内容颜色</remarks>
public int ContentColor
{
get
{
return contentColor;
}
set
{
contentColor = value;
}
}
/// <summary>
/// 结尾颜色
/// </summary>
/// <remarks>结尾颜色</remarks>
public int FinalityColor
{
get
{
return finalityColor;
}
set
{
finalityColor = value;
}
}
public void OutputExcel()
{
if (StripProgress == null)
{
StripProgress += new Progress(NullEventProgress);
}
if (OutError == null)
{
OutError += new Error(NullEventError);
}
Excel.Application excel;
//int rowIndex = 4;
int colIndex = 1;
Excel._Workbook xBk;
Excel._Worksheet xSt;
excel = new Excel.ApplicationClass(); ;
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
DataRow[] myRows;
Int32 RowCount;
try
{
myRows = datatab.Select();
RowCount = myRows.Length;
//
//设置列标题
//
foreach (DataColumn col in datatab.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
//输出表内容
if (RowCount <= 100)
{
Int32 ProgressBarMax = 0, ProgressBarPint = 0;
int i = 4;
ProgressBarMax = myRows.Length;
ProgressBarMax = Convert.ToInt32(Math.Floor(Convert.ToDecimal(100 / ProgressBarMax)));
ProgressBarPint = 0;
int insurance = 1;
foreach (DataRow myRow in myRows)
{
for (ProgressBarPint = 1; ProgressBarPint <= ProgressBarMax; ProgressBarPint++)
{
if (insurance <= 100) StripProgress(this, new EventArgs());
insurance++;
}
i++;
for (int j = 0; j < colIndex - 1; j++)
{
excel.Cells[i, j + 2] = myRow[j].ToString().Trim();
}
}
}
else
{
Int32 ProgressBarMax = 0, ProgressBarPint = 0;
int i = 4;
ProgressBarMax = myRows.Length;
ProgressBarMax = Convert.ToInt32(Math.Floor(Convert.ToDecimal(ProgressBarMax / 100)));
ProgressBarPint = 0;
int insurance = 1;
foreach (DataRow myRow in myRows)
{
if (ProgressBarPint <= ProgressBarMax) ProgressBarPint++;
else ProgressBarPint = 1;
if (ProgressBarMax == ProgressBarPint)
{
if (insurance <= 100) StripProgress(this, new EventArgs());
insurance++;
}
i++;
for (int j = 0; j < colIndex - 1; j++)
{
excel.Cells[i, j + 2] = myRow[j].ToString().Trim();
}
}
}
int rowSum = RowCount + 5;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
excel.Cells[rowSum, 3] = RowCount.ToString().Trim();
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//
//设置标题颜色
//
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Interior.ColorIndex = captionColor;//设置为无色，共计有56种
//
//设置内容区域颜色
//
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum - 1, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum - 1, colIndex]).Interior.ColorIndex = contentColor;//设置为无色，共计有56种
//
//设置尾部合计行颜色
//
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = finalityColor;//设置为无色，共计有56种
//
//取得整个报表的标题
//
excel.Cells[2, 2] = tabname;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
if (selfadapting)
{
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
}
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
if (format)
{
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
}
//显示效果
//
excel.Visible = true;
}
catch (Exception ex)
{
OutError(this, new EventArgs(),ex.Message.ToString());
return;
}
finally //释放，不知道这样释放对不对~~
{
/*方法一
xBk = null;
xSt = null;
excel = null;
GC.Collect();
*/
//方法二
ReleaseCOM<Excel._Workbook>(ref xBk);
ReleaseCOM<Excel._Worksheet>(ref xSt);
ReleaseCOM<Excel.Application>(ref excel);
}
}
private void ReleaseCOM<T>(ref T pObj)//资源释放泛型方法
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
}
catch
{
OutError(this, new EventArgs(), "释放资源时发生错误！");
throw new Exception("释放资源时发生错误！");
}
finally
{
pObj = default(T);
}
}
private void NullEventProgress(object sender, EventArgs e)
{
//进度条空事件
}
private void NullEventError(object sender, EventArgs e, string ErrorMessage)
{
//错误空事件
}
}
}
因为啥没人搭理我啊是不是偶的水平太洼了