using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Collections;
namespace ConSystemComponent.DataAccess
{
public class ExcelOperator
{
/// <summary>
///将DataTable数据导出到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name="dt">将要导出的DataTable</param>
/// <param name="savePath">生成Excel文件的保存路径(末尾不要加\\)</param>
/// <returns>返回生成Excel文件的完整路径</returns>
public string DataTableToExcel(DataTable dt, string savePath)
{
DateTime now = DateTime.Now;
string FileName = now.Year.ToString().PadLeft(2) + now.Month.ToString().PadLeft(2, '0') + now.Day.ToString().PadLeft(2, '0') + "-" + now.Hour.ToString().PadLeft(2, '0') + now.Minute.ToString().PadLeft(2, '0') + now.Second.ToString().PadLeft(2, '0');
//验证strFileName是否为空或值无效
if (FileName.Trim() == "")
{ return "保存路径不正确!"; }
//if (!Directory.Exists(savePath))
//{
// return "保存路径不正确!";
//}
FileName = FileName + ".xls";
//定义表格内数据的行数和列数
int rowscount = dt.Rows.Count;
int colscount = dt.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
return "没有数据可供保存!";
}
//列数必须大于0
if (colscount <= 0)
{
return "没有数据可供保存!";
}
//行数不可以大于65536
if (rowscount > 65536)
{
return "数据记录数太多(最多不能超过65536条),不能保存!";
}
//列数不可以大于255
if (colscount > 255)
{
return "数据记录行数太多,不能保存 ";
}
//验证以fileName命名的文件是否存在,如果存在删除它
FileInfo file = new FileInfo(FileName);
if (file.Exists)
{
try
{
file.Delete();
}
catch
{
return "试图删除现有文件" + FileName + "失败!";
}
}
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
//设置单元格格式
objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[1, displayColumnsCount]).Font.Bold = true;
objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[1, displayColumnsCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objExcel.Cells[1, displayColumnsCount] = Arr[i];
displayColumnsCount++;
}
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dt.Rows.Count - 1; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
objExcel.Cells[row + 2, displayColumnsCount] = dt.Rows[row][col].ToString().Trim();
if (dt.Columns[col].ColumnName == "Attribute11" || dt.Columns[col].ColumnName == "Attribute12")
{
if (dt.Rows[row][col].ToString().Trim() == "1")
{
objExcel.Cells[row + 2, displayColumnsCount] = "发送";
}
else
{
objExcel.Cells[row + 2, displayColumnsCount] = "不发送";
}
}
displayColumnsCount++;
}
}
//设置单元格格式
Excel.Range excelRange = objsheet.get_Range(objsheet.Cells[1, 1], objsheet.Cells[rowscount+1, colscount]);
excelRange.Borders.LineStyle = 1;
//保存文件
//objWorkbook.SaveAs(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value);
objExcel.Save(FileName);
}
catch (Exception error)
{
return "生成Excel文件出错:" + error.Message;
}
finally
{
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
return FileName;
}
/// <summary>
/// 获取表头信息
/// </summary>
public ArrayList Arr
{
get
{
ArrayList arr=new ArrayList();
arr.Add("公司");
arr.Add("name");
arr.Add("age");
arr.Add("地址");
arr.Add("公司电话");
arr.Add("手机号码");
arr.Add("邮箱地址A");
arr.Add("邮箱地址B");
arr.Add("状态1");
arr.Add("状态2");
arr.Add("编号");
return arr;
}
}
}
}