//导出控件Aspose.Cells 必须引用该控件
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Collections.Generic;
using System.IO;
using Aspose.Cells;
namespace BLL
{
public class ImportExportExcel
{
private static string outFileName = "";
private static Workbook workbook = null;
private static Worksheet worksheet = null;
private static void AddTitle(string title, int columnCount)
{ //创建标题
//合并单元格
worksheet.Cells.Merge(0, 0, 1, columnCount);
worksheet.Cells.Merge(1, 0, 1, columnCount);
Cell cell_title = worksheet.Cells[0, 0];
Cell cell_date = worksheet.Cells[1, 0];
cell_title.PutValue(title, true);
cell_title.Style.HorizontalAlignment = TextAlignmentType.Center;
cell_title.Style.Font.Name = "黑体";
cell_title.Style.Font.Size = 18;
cell_title.Style.Font.IsBold = true;
cell_date.PutValue("查询时间:" + DateTime.Now.ToLocalTime(), true);
cell_date.SetStyle(cell_title.Style);
}
private static void AddHeader(DataTable dt)
{ //生成列名标题
Cell cell = null;
for (int col = 0; col < dt.Columns.Count; col++)
{
cell = worksheet.Cells[2, col];
cell.PutValue(dt.Columns[col].ColumnName);
cell.Style.Font.IsBold = true;
}
}
private static void AddBody(DataTable dt)
{ //生成指定数据到EXCEL中;
long ll_count;
ll_count = dt.Rows.Count;
for (int r = 0; r < ll_count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
worksheet.Cells[r + 3, c].PutValue(dt.Rows[r][c].ToString());
}
}
}
public static string DatatableToExcel(DataTable dt, string fileTitle, string sheetName,string fileName)
{
try
{
workbook = new Workbook();
workbook.Worksheets.Clear();
string ls_path_dir;
ls_path_dir = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string ls_filename;
string ls_filepath;
//按时间生成指定文件名
ls_filename = fileName + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//生成文件在服务器的地址
ls_filepath = ls_path_dir + "Exportfile\\" + ls_filename;
//判断文件是否存在,存在则删除文件;
if (File.Exists(ls_filepath))
{ File.Delete(ls_filepath); }
if(sheetName.Length == 0)
{ sheetName = "Sheet"; }
workbook.Worksheets.Add(sheetName);
worksheet = workbook.Worksheets[0];
//worksheet.Name = sheetName;
outFileName = ls_filepath;
AddTitle(fileTitle, dt.Columns.Count);
AddHeader(dt);
AddBody(dt);
//保存生成文件
worksheet.AutoFitColumns();
worksheet.AutoFitRows();
workbook.Save(outFileName);
return ls_filepath;
}
catch (SystemException)
{ return "";
}
}
public static DataTable ExcelToDatatable(string excelFileName)
{
Workbook book = new Workbook();
book.Open(excelFileName);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
//获取excel中的数据保存到一个datatable中
DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
//dt_Import.
return dt_Import;
}
}
}