【C#】Aspose.Words.dll、Aspose.Cells.dll使用汇总

一、Aspose.Cells.dll

1、从DataTable导入、导出Excel

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;
public class AsposeExcel
{
    private string outFileName = "";
    private string fullFilename = "";
    private Workbook book = null;
    private Worksheet sheet = null;
    public AsposeExcel(string outfilename, string tempfilename) //导出构造数
    {
        outFileName = outfilename;
        book = new Workbook();
        // book.Open(tempfilename);这里我们暂时不用模板
        sheet = book.Worksheets[0];
    }
    public AsposeExcel(string fullfilename) //导入构造数
    {
        fullFilename = fullfilename;
        // book = new Workbook();
        // book.Open(tempfilename);
        // sheet = book.Worksheets[0];
    }
    private void AddTitle(string title, int columnCount)
    {
        sheet.Cells.Merge(0, 0, 1, columnCount);
        sheet.Cells.Merge(1, 0, 1, columnCount);
        Cell cell1 = sheet.Cells[0, 0];
        cell1.PutValue(title);
        cell1.Style.HorizontalAlignment = TextAlignmentType.Center;
        cell1.Style.Font.Name = "黑体";
        cell1.Style.Font.Size = 14;
        cell1.Style.Font.IsBold = true;
        Cell cell2 = sheet.Cells[1, 0];
        cell1.PutValue("查询时间：" + DateTime.Now.ToLocalTime());
        cell2.SetStyle(cell1.Style);
    }
    private void AddHeader(DataTable dt)
    {
        Cell cell = null;
        for (int col = 0; col < dt.Columns.Count; col++)
        {
            cell = sheet.Cells[0, col];
            cell.PutValue(dt.Columns[col].ColumnName);
            cell.Style.Font.IsBold = true;
        }
    }
    private void AddBody(DataTable dt)
    {
        for (int r = 0; r < dt.Rows.Count; r++)
        {
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                sheet.Cells[r + 1, c].PutValue(dt.Rows[R]­[c].ToString());
            }
        }
    }
    //导出------------下一篇会用到这个方法
    public Boolean DatatableToExcel(DataTable dt)
    {
        Boolean yn = false;
        try
        {
            //sheet.Name = sheetName;
            //AddTitle(title, dt.Columns.Count);
            //AddHeader(dt);
            AddBody(dt);
            sheet.AutoFitColumns();
            //sheet.AutoFitRows();
            book.Save(outFileName);
            yn = true;
            return yn;
        }
        catch (Exception e)
        {
            return yn;
            // throw e;
        }
    }
    public DataTable ExcelToDatatalbe()//导入
    {
        Workbook book = new Workbook();
        book.Open(fullFilename);
        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;
    }
}

https://www.cnblogs.com/robinli/p/3453660.html

2、设置单元格样式

//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Workbook object
int i = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
//Adding the current system date to "A1" cell
worksheet.Cells["A1"].PutValue(DateTime.Now);
//Getting the Style of the A1 Cell
Style style = worksheet.Cells["A1"].GetStyle();
//Setting the display format to number 15 to show date as "d-mmm-yy"
style.Number = 15;
//Applying the style to the A1 cell
worksheet.Cells["A1"].SetStyle(style);
//Adding a numeric value to "A2" cell
worksheet.Cells["A2"].PutValue(20);
//Getting the Style of the A2 Cell
style = worksheet.Cells["A2"].GetStyle();
//Setting the display format to number 9 to show value as percentage
style.Number = 9;
//Applying the style to the A2 cell
worksheet.Cells["A2"].SetStyle(style);
//Adding a numeric value to "A3" cell
worksheet.Cells["A3"].PutValue(2546);
//Getting the Style of the A3 Cell
style = worksheet.Cells["A3"].GetStyle();
//Setting the display format to number 6 to show value as currency
style.Number = 6;
//Applying the style to the A3 cell
worksheet.Cells["A3"].SetStyle(style);
//Saving the Excel file
workbook.Save("C:\\book1.xls", SaveFormat.Excel97To2003);
当然开发人员还可以为单元格设置自定义显示样式，下面的代码就怎么设置单元格自定义显示样式做举例：
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
//Adding the current system date to "A1" cell
worksheet.Cells["A1"].PutValue(DateTime.Now);
//Getting the style of A1 cell
Style style = worksheet.Cells["A1"].GetStyle();
//Setting the custom display format to show date as "d-mmm-yy"
style.Custom = "d-mmm-yy";
//Applying the style to A1 cell
worksheet.Cells["A1"].SetStyle(style);
//Adding a numeric value to "A2" cell
worksheet.Cells["A2"].PutValue(20);
//Getting the style of A2 cell
style = worksheet.Cells["A2"].GetStyle();
//Setting the custom display format to show value as percentage
style.Custom = "0.0%";
//Applying the style to A2 cell
worksheet.Cells["A2"].SetStyle(style);
//Adding a numeric value to "A3" cell
worksheet.Cells["A3"].PutValue(2546);
//Getting the style of A3 cell
style = worksheet.Cells["A3"].GetStyle();
//Setting the custom display format to show value as currency
style.Custom = "£#,##0;[Red]$-#,##0";
//Applying the style to A3 cell
worksheet.Cells["A3"].SetStyle(style);
//Saving the Excel file
workbook.Save("C:\\book1.xls", SaveFormat.Excel97To2003);

 

using Aspose.Cells;    
using System.Drawing;
 
Workbook workbook=new Workbook();
Style style=workbook.Styles[workbook.Styles.Add()];
WorkSheet worksheet=workbook.WorkSheet[0];
//字体样式
style.Font.Color = Color.Red;//字体颜色
style.Font.Size = 10;//字体大小
style.Font.IsBold = true;//字体加粗
style.Font.Name = "宋体";//文字字体 
 
//单元格样式
//单元格背景颜色
style.ForegroundColor = Color.Red;//红色
style.ForegroundColor = Color.Gray;//灰色
style.ForegroundColor = Color.Yellow;//黄色
style.ForegroundColor = Color.Magenta;//紫红色
style.ForegroundColor = Color.Orange;//橙色
style.ForegroundColor = Color.Pink;//粉红
style.ForegroundColor = Color.Aqua;//浅蓝
style.ForegroundColor = Color.PaleGreen;//浅绿
 
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Center;//水平居中
style.IsTextWrapped = true;//单元格内容自动换行
//边框样式
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //左边框 
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //右边框  
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //上边框  
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //下边框
 
Range range= worksheet.Cells.CreateRange(0, 0, 1, 1);//第一行第一列单元格
range.ApplyStyle(style, new StyleFlag() { All=true});

https://www.cnblogs.com/mahatmasmile/p/7806118.html

https://blog.csdn.net/qq_38974638/article/details/108631101

3、查找指定的单元格

//程序集 Aspose.Cells.dll, v5.3.1.0
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Linq;
namespace OperateCell
{
    class Program
    {
        static void Main(string[] args)
        {
 
        }
        public static int GetMaxColumn(Cell cell, Cells cells)
        {
            int i = 0;
            int maxColumn = 0;
            while (cell != null && cells.GetCell(cell.Row, cell.Column + i + 1) != null)
            {
                i++;
                maxColumn = i + cell.Column;
            }
            return maxColumn;
        }
        /// <summary>
        ///  利用递归通过关键字寻找要cell
        /// </summary>
        /// <param name="cells">开始寻找的对象cells</param>
        /// <param name="keyWord">应该包含的关键字</param>
        /// <param name="onContains">不应该包含的关键字</param>
        /// <param name="cellBegin">开始寻找的cell</param>
        /// <returns>返回需要寻找的cell</returns>
        public static Cell FindCell(Cells cells, string[] keyWord, string[] noContains, Cell cellBegin)
        {
            //通过第一关键字定位到一个cell
            Cell firstCell = cells.FindStringContains(keyWord[0], cellBegin);
            if (firstCell == null)
                return null;
            //假设全部都包含
            bool allContainKeyWord = true;
            //假设全部都不包含
            bool allNoConain = true;
            for (int i = 1; i < keyWord.Length; i++)
            {
                if (!firstCell.Value.ToString().Contains(keyWord[i]))
                {
                    //有一个不包含，那么allContainKeyWord就为false
                    allContainKeyWord = false;
                    break;
                }
            }
            //keyword全部包含
            if (allContainKeyWord)
            {
                for (int i = 0; i < noContains.Length; i++)
                {
                    //判断寻找到的是否包含不应该包含的
                    if (firstCell.Value.ToString().Contains(noContains[i]))
                    {
                        //有一个不应该包含的，就为false
                        allNoConain = false;
                        break;
                    }
                }
                //没有不包含的
                if (allNoConain)
                    return firstCell;
                else
                    //继续寻找，通过firstcell寻找
                    return FindCell(cells, keyWord, noContains, firstCell);
            }
            else //如果keyword有不包含的，重新找，从firstcell开始找
                return FindCell(cells, keyWord, noContains, firstCell);
 
        }
    }
}

 

4、删除行、列、指定单元格

（1）删除单行

Workbook workbook=new Workbook();
WorkSheet worksheet=workbook.WorkSheet[0];
Cells cells=worksheet.Cells;
//删除第一行
cells.DeleteRow(0);

 

（2）删除多行

Workbook workbook=new Workbook();
WorkSheet worksheet=workbook.WorkSheet[0];
Cells cells=worksheet.Cells;
//删除第一行起两行的单元格
cells.DeleteRows(0,2);

 

（3）删除单列

Workbook workbook=new Workbook();
WorkSheet worksheet=workbook.WorkSheet[0];
Cells cells=worksheet.Cells;
//删除第一列
cells.DeleteColumn(0);

 

（4）删除多列

    Workbook workbook=new Workbook();
    WorkSheet worksheet=workbook.WorkSheet[0];
    Cells cells=worksheet.Cells;
    //删除第一列起两列的单元格
    cells.DeleteColumns(0,2,false);

 

（5）删除指定单元格

Workbook workbook=new Workbook();
WorkSheet worksheet=workbook.WorkSheet[0];
Cells cells=worksheet.Cells;
//删除第一行第一列至第二行第二列之间的4个单元格
cells.DeleteRange(0,0,1,1);

https://blog.csdn.net/qq_38974638/article/details/111731521

 

5、Chart图表

（1）设置chart的y坐标轴显示值

https://www.cnblogs.com/muer/p/yaxle.html

（2） 导出Excel数据量大时，生成图表报错：OutOfMemoryException

图表数据来源于Excel表格的一列，有2w个数据，超出了图表容量上限。改成每个图表绘制1000个数据就不会报错了。

 

二、Aspose.Words.dll

复制多个表格

复制多个表格，并将复制的表格分页插入原有表格后面，使其一个表格一页：

https://blog.csdn.net/qq_38974638/article/details/108201429

 

二、拆分单元格、复制行、插入行

 

 

 private void button3_Click(object sender, EventArgs e)
        {
            object savePathWord ="row.docx";
            File.Copy("rowtemplate.docx", savePathWord.ToString(),true);


            Aspose.Words.Document doc = new Aspose.Words.Document(savePathWord.ToString());
            Aspose.Words.DocumentBuilder builder = new Aspose.Words.DocumentBuilder(doc);  


            NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);




            Aspose.Words.Tables.Table table = allTables[0] as Aspose.Words.Tables.Table;//拿到第一个表格


            for (int i = 0; i < 5; i++)
            {
                var roww = table.Rows[2];
                //var row = table.LastRow.Clone(true);
                var row = roww.Clone(true);//复制第三行(绿色行)
                table.Rows.Insert(2 + i, row);//将复制的行插入当前行的上方
            }


            //var row3=table.Rows[3];
            //row3.Cells[0].AppendChild(new Paragraph(doc).AppendChild(new Run(doc, "test"))); //报错
            builder.MoveToCell(0,3,0,0); //移动到第一个表格的第四行第一个格子
            builder.Write("test"); //单元格填充文字


            //不能用此方法 表格会错乱
            //var row1 = CreateRow(7, new string[] { "1" }, doc);
            //var row2 = CreateRow(12, new string[] { "2" }, doc);
            //table.Rows.Insert(3, row1);
            //table.Rows.Add(row2);
            


            doc.Save(savePathWord.ToString());
            MessageBox.Show("ok");
            System.Diagnostics.Process.Start(savePathWord.ToString());
            
        }


        Aspose.Words.Tables.Cell CreateCell(string value,Document doc)
        {
            Aspose.Words.Tables.Cell c1 = new Aspose.Words.Tables.Cell(doc);
            c1.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
            c1.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
            Aspose.Words.Paragraph p = new Paragraph(doc);
            p.AppendChild(new Run(doc,value));
            c1.AppendChild(p);
            return c1;
        }


        Aspose.Words.Tables.Row CreateRow(int columnCount,string[] columnValues,Document doc)
        {
            Aspose.Words.Tables.Row r2 = new Aspose.Words.Tables.Row(doc);
            for (int i = 0; i < columnCount; i++)
            {
                if (columnValues.Length >i)
                {
                    var cell = CreateCell(columnValues[i], doc);
                    r2.Cells.Add(cell);
                }
                else
                {
                    var cell = CreateCell("", doc);
                    r2.Cells.Add(cell);
                }
                
            }
            return r2;

        }

 

出处：https://www.pianshen.com/article/72321769650/