Estsh-Yiri/Estsh.Core/Util/AsposeExcelTools.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Collections;
using Aspose.Cells;

namespace Estsh.Core.Util
{
    /// <summary>
    /// Excel工具类
    /// </summary>
    public class AsposeExcelTools
    {
        /// <summary>
        /// 将Datatable转化为Excel，不带标题
        /// </summary>
        /// <param name="datatable"></param>
        /// <returns></returns>
        public static Workbook DataTableToExcel(DataTable datatable)
        {
            if (datatable == null && datatable.Rows.Count < 0)
            {
                return null;
            }
            try
            {
                Workbook workbook = new Workbook();
                Worksheet sheet = workbook.Worksheets[0];
                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 (Exception e)
                    {
                        return null;
                    }
                }
                return workbook;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        /// <summary>
        /// 将Datatable转化为Excel，带标题
        /// </summary>
        /// <param name="datatable"></param>
        /// <returns></returns>
        public static Workbook DataTableToExcel2(DataTable datatable)
        {
            if (datatable == null && datatable.Rows.Count < 0)
            {
                return null;
            }
            Workbook wb = new Workbook();
            try
            {
                
                //为单元格添加样式    
                Style style = wb.CreateStyle();
                //设置居中
                style.HorizontalAlignment = TextAlignmentType.Center;
                //设置背景颜色
                style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
                style.Pattern = 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);
                return wb;
            }
            catch (Exception e)
            {
                return null;
            }

        }

        /// <summary>
        /// Excel文件转换为DataTable.只转化第一个Sheet
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static DataTable ExcelFileToDataTable(Stream stream)
        {
            DataTable dt = null;
            try
            {
                Workbook workbook = new Workbook(stream);
                Worksheet worksheet = workbook.Worksheets[0];
                dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        /// <summary>
        /// Excel文件转换为DataTable.只转化第一个Sheet,将表格第一列作为列名
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static DataTable ExcelFileToDataTable(Stream stream, bool exportColumnName)
        {
            DataTable dt = null;
            try
            {
                Workbook workbook = new Workbook(stream);
                Worksheet worksheet = workbook.Worksheets[0];
                dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1,exportColumnName);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        /// <summary>
        /// 将Excel转化为DataSet
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static DataSet ExcelFileToDataSet(Stream stream)
        {
            DataSet ds = new DataSet();
            try
            {
                Workbook workbook = new Workbook(stream);
                for (int index = 0; index < workbook.Worksheets.Count; index++)
                {
                    DataTable dt = ExcelFileToDataTable(workbook.Worksheets[index]);
                    ds.Tables.Add(dt);
                }
                
                return ds;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        /// <summary>
        /// 将Excel中的sheet转化为Datatable
        /// </summary>
        /// <param name="worksheet"></param>
        /// <returns></returns>
        public static DataTable ExcelFileToDataTable(Worksheet worksheet)
        {
            DataTable dt = null;
            try
            {
                dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
        }
    }
}