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);
}
/// <summary>
/// 获取Excel文件里面的图片，并把图片存储到图片对象列表中
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="pictures">图片对象列表</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
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);
}
}
/// <summary>
/// Excel文件转换为DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
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);
datatable.TableName = worksheet.Name;//记录Sheet的名称
//-------------图片处理-------------
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);
}
}
/// <summary>
/// 获取Excel文件里面的图片，并把图片存储到图片对象列表中
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="pictures">图片对象列表</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
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 (Exception ex)
{
error = ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
return(false);
}
}
/// <summary>
/// 把所有Sheet里面的内容，分别导入到不同的DataTable对象里面
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatables">DataTable对象集合</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
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];
try
{
//为了避免有个别Sheet出现错误而导致全部不能出来，这里进行忽略处理
datatables[i] = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName);
datatables[i].TableName = worksheet.Name;//记录Sheet的名称
//图片处理
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;
}
}
}
catch (Exception ex)
{
error = ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
continue;
}
}
return(true);
}
catch (Exception ex)
{
error = ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
return(false);
}
}
/// <summary>
/// 把所有Sheet里面的内容，分别导入到不同的DataTable对象里面
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatables">DataTable对象集合</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
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);
}
}