using Aspose.Cells.Drawing;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms.VisualStyles;
namespace ProjectBase.Controls.Others
{
internal class AsposeExcelTools
{
///
/// DataTabel转换成Excel文件
///
/// DataTable对象
/// 返回的字段列表
/// Excel文件的全路径
/// 错误信息:返回错误信息,没有错误返回""
/// 开始列的序号
/// 开始的行序号
/// true:函数正确执行 false:函数执行错误
public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
{
error = "";
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fromfile);
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------插入数据-------------
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
cells.InsertRow(beginRow);
for (int i = 0; i < colNameList.Count; i++)
{
string colName = colNameList[i].ToString();
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (colName == datatable.Columns[j].ColumnName)
{
object temp = row[datatable.Columns[j].ColumnName];
cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
break;
}
}
}
}
catch (System.Exception e)
{
error = error + " DataTableInsertToExcel: " + e.Message;
}
}
//-------------保存-------------
workbook.Save(fromfile);
return true;
}
///
/// 把DataTabel转换成Excel文件
///
/// DataTable对象
/// 目标文件路径,Excel文件的全路径
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath);
return true;
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
}
///
/// 把DataTabel转换成Excel文件
///
/// DataTable对象
/// 目标文件路径,Excel文件的全路径
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error)
{
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
//为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
style.Pattern = Aspose.Cells.BackgroundType.Solid;
style.Font.IsBold = true;
int rowIndex = 0;
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
foreach (DataRow row in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
}
for (int k = 0; k < datatable.Columns.Count; k++)
{
wb.Worksheets[0].AutoFitColumn(k, 0, 150);
}
wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
wb.Save(filepath);
return true;
}
catch (Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
}
///
/// DataSet导出到Excel文件
/// 默认导出第一页的数据
///
/// DataSet
/// Excel文件的全路径
/// 错误信息:返回错误信息,没有错误返回""
/// true:函数正确执行 false:函数执行错误
public static bool DataSetToExcel(DataSet dataset, string filepath, out string error)
{
if (DataTableToExcel(dataset.Tables[0], filepath, out error))
{
return true;
}
else
{
error = "DataTableToExcel: " + error;
return false;
}
}
///
/// Excel文件转换为DataTable(第一个sheet)
///
/// Excel文件的全路径
/// DataTable:返回值
/// 错误信息:返回错误信息,没有错误返回""
/// true:函数正确执行 false:函数执行错误
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
{
bool exportColumnName = true;
return ExcelFileToDataTable(filepath, out datatable, exportColumnName, out error);
}
///
/// Excel文件转换为DataTable.
///
/// Excel文件的全路径
/// DataTable:返回值
/// 错误信息:返回错误信息,没有错误返回""
/// true:函数正确执行 false:函数执行错误
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, bool exportColumnName, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName);
//-------------图片处理-------------
Aspose.Cells.Drawing.PictureCollection pictures = worksheet.Pictures;
if (pictures.Count > 0)
{
string error2 = "";
if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false)
{
error = error + error2;
}
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
///
/// 把所有Sheet里面的内容,分别导入到不同的DataTable对象里面
///
/// Excel文件的全路径
/// DataTable对象集合
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, out string error)
{
bool exportColumnName = true;
return ExcelFileToDataTables(filepath, out datatables, exportColumnName, out error);
}
///
/// 把所有Sheet里面的内容,分别导入到不同的DataTable对象里面
///
/// Excel文件的全路径
/// DataTable对象集合
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, bool exportColumnName, out string error)
{
error = "";
datatables = null;
int nSheetsCount = 0;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatables = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath);
nSheetsCount = workbook.Worksheets.Count;
datatables = new DataTable[nSheetsCount];
for (int i = 0; i < nSheetsCount; i++)
{
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
datatables[i] = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName);
//-------------图片处理-------------
Aspose.Cells.Drawing.PictureCollection pictures = worksheet.Pictures;
if (pictures.Count > 0)
{
string error2 = "";
if (InsertPicturesIntoDataTable(pictures, datatables[i], out datatables[i], out error2) == false)
{
error = error + error2;
}
}
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
///
/// Excel文件转换为DataTable.(指定开始行列,以及导入的记录行数)
///
/// Excel文件的全路径
/// DataTable:返回值
/// 起始行
/// 起始列
/// 导入行数
/// 列数
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, bool exportColumnName, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTableAsString(iFirstRow, iFirstCol, rowNum + 1, colNum + 1, exportColumnName);
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
///
/// Excel文件转换为DataSet.
///
/// Excel文件的全路径
/// DataSet:返回值
/// 错误信息:返回错误信息,没有错误返回""
/// true:函数正确执行 false:函数执行错误
public static bool ExcelFileToDataSet(string filepath, out DataSet dataset, out string error)
{
DataTable datatable = new DataTable();
dataset = new System.Data.DataSet();
if (ExcelFileToDataTable(filepath, out datatable, out error))
{
dataset.Tables.Add(datatable);
return true;
}
else
{
error = "ExcelFileToDataSet: " + error;
return false;
}
}
///
/// 获取Excel文件里面的图片,并把图片存储到图片对象列表中
///
/// Excel文件的全路径
/// 图片对象列表
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool GetPicturesFromExcelFile(string filepath, out PictureCollection[] pictures, out string error)
{
error = "";
pictures = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
pictures = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath);
pictures = new Aspose.Cells.Drawing.PictureCollection[workbook.Worksheets.Count];
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
//pictures.Add();
pictures[i] = workbook.Worksheets[i].Pictures;
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
private static bool InsertPicturesIntoDataTable(Aspose.Cells.Drawing.PictureCollection pictures, DataTable fromdatatable, out DataTable datatable, out string error)
{
error = "";
datatable = fromdatatable;
//把图片按位置插入Table中
DataRow[] rows = datatable.Select();
foreach (Picture picture in pictures)
{
try
{
System.Console.WriteLine(picture.GetType().ToString());
//----把图片转换成System.Drawing.Image----
MemoryStream mstream = new MemoryStream();
mstream.Write(picture.Data, 0, picture.Data.Length);
System.Drawing.Image image = System.Drawing.Image.FromStream(mstream);
//----Image放入DataTable------
//datatable.Columns[picture.UpperLeftColumn].DataType = image.GetType();
rows[picture.UpperLeftRow][picture.UpperLeftColumn] = image;
}
catch (System.Exception e)
{
error = error + " InsertPicturesIntoDataTable: " + e.Message;
}
}
return true;
}
///
/// 把Excel文件内容导入到List对象
///
/// Excel文件的全路径
/// 列表对象
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool ExcelFileToLists(string filepath, out IList[] lists, out string error)
{
error = "";
lists = null;
DataTable datatable = new DataTable();
IList list = new ArrayList();
Aspose.Cells.Drawing.PictureCollection[] pictures;
if (ExcelFileToDataTable(filepath, out datatable, out error) && GetPicturesFromExcelFile(filepath, out pictures, out error))
{
lists = new ArrayList[datatable.Rows.Count];
//------------DataTable转换成IList[]--------------
//数据
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
lists[nRow] = new ArrayList(datatable.Columns.Count);
for (int i = 0; i <= datatable.Columns.Count - 1; i++)
{
lists[nRow].Add(row[i]);
}
nRow++;
}
//图片
for (int i = 0; i < pictures.Length; i++)
{
foreach (Picture picture in pictures[i])
{
try
{
//----把图片转换成System.Drawing.Image----
//MemoryStream mstream = new MemoryStream();
//mstream.Write(picture.Data, 0, picture.Data.Length);
//System.Drawing.Image image = System.Drawing.Image.FromStream(mstream);
//----Image放入IList------
//图片有可能越界
if (picture.UpperLeftRow <= datatable.Rows.Count && picture.UpperLeftColumn <= datatable.Columns.Count)
{
lists[picture.UpperLeftRow][picture.UpperLeftColumn] = picture.Data;
}
}
catch (System.Exception e)
{
error = error + e.Message;
}
}
}
}
else
{
return false;
}
return true;
}
///
/// 把列表导出到Excle文件
///
/// Excel文件的全路径
/// 列表对象
/// 错误信息:返回错误信息,没有错误返回""
///
public static bool ListsToExcelFile(string filepath, IList[] lists, out string error)
{
error = "";
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
sheet.Pictures.Clear();
cells.Clear();
foreach (IList list in lists)
{
for (int i = 0; i <= list.Count - 1; i++)
{
try
{
System.Console.WriteLine(i.ToString() + " " + list[i].GetType());
if (list[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//插入图片数据
System.Drawing.Image image = (System.Drawing.Image)list[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(list[i]);
}
}
catch (System.Exception e)
{
error = error + e.Message;
}
}
nRow++;
}
workbook.Save(filepath);
return true;
}
}
}