123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572 |
- 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
- {
- /// <summary>
- /// DataTabel转换成Excel文件
- /// </summary>
- /// <param name="datatable">DataTable对象</param>
- /// <param name="colNameList">返回的字段列表</param>
- /// <param name="fromfile">Excel文件的全路径</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <param name="beginColumn">开始列的序号</param>
- /// <param name="beginRow">开始的行序号</param>
- /// <returns>true:函数正确执行 false:函数执行错误</returns>
- 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;
- }
- /// <summary>
- /// 把DataTabel转换成Excel文件
- /// </summary>
- /// <param name="datatable">DataTable对象</param>
- /// <param name="filepath">目标文件路径,Excel文件的全路径</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns></returns>
- 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;
- }
- }
- /// <summary>
- /// 把DataTabel转换成Excel文件
- /// </summary>
- /// <param name="datatable">DataTable对象</param>
- /// <param name="filepath">目标文件路径,Excel文件的全路径</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns></returns>
- 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;
- }
- }
- /// <summary>
- /// DataSet导出到Excel文件
- /// 默认导出第一页的数据
- /// </summary>
- /// <param name="dataset">DataSet</param>
- /// <param name="filepath">Excel文件的全路径</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns>true:函数正确执行 false:函数执行错误</returns>
- 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;
- }
- }
- /// <summary>
- /// Excel文件转换为DataTable(第一个sheet)
- /// </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, out string error)
- {
- bool exportColumnName = true;
- return ExcelFileToDataTable(filepath, out datatable, exportColumnName, out error);
- }
- /// <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);
- //-------------图片处理-------------
- 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>
- /// 把所有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, out string error)
- {
- bool exportColumnName = true;
- return ExcelFileToDataTables(filepath, out datatables, exportColumnName, out error);
- }
- /// <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;
- }
- }
- /// <summary>
- /// Excel文件转换为DataTable.(指定开始行列,以及导入的记录行数)
- /// </summary>
- /// <param name="filepath">Excel文件的全路径</param>
- /// <param name="datatable">DataTable:返回值</param>
- /// <param name="iFirstRow">起始行</param>
- /// <param name="iFirstCol">起始列</param>
- /// <param name="rowNum">导入行数</param>
- /// <param name="colNum">列数</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns></returns>
- 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;
- }
- }
- /// <summary>
- /// Excel文件转换为DataSet.
- /// </summary>
- /// <param name="filepath">Excel文件的全路径</param>
- /// <param name="dataset">DataSet:返回值</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns>true:函数正确执行 false:函数执行错误</returns>
- 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;
- }
- }
- /// <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;
- }
- }
- 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文件内容导入到List对象
- /// </summary>
- /// <param name="filepath">Excel文件的全路径</param>
- /// <param name="lists">列表对象</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns></returns>
- 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;
- }
- /// <summary>
- /// 把列表导出到Excle文件
- /// </summary>
- /// <param name="filepath">Excel文件的全路径</param>
- /// <param name="lists">列表对象</param>
- /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
- /// <returns></returns>
- 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;
- }
- }
- }
|