使用Aspose.Cells的基础知识整理

最新推荐文章于 2018-04-26 18:20:00 发布

沐NeMo

最新推荐文章于 2018-04-26 18:20:00 发布

阅读量1.8k

点赞数

分类专栏： 2.VB.Net 文章标签： excel string winform exception import null

2.VB.Net 专栏收录该内容

120 篇文章 4 订阅

订阅专栏

http://www.cnblogs.com/kenblove/archive/2009/01/07/1371104.html

这两天用Aspose.Cells构建一个Excel报表,感觉这个组件还比较好用.记录一下常用的使用知识:

1.创建Workbook和Worksheet

workbook&worksheet1
Workbook wb = new Workbook();
wb.Worksheets.Clear();
wb.Worksheets.Add("New Worksheet1");//New Worksheet1是Worksheet的name
Worksheet ws = wb.Worksheets[0];

如果直接用下边两句则直接使用默认的第一个Worksheet:

workbook&worksheet2
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];

2.给Cell赋值设置背景颜色并加背景色:

cell1
Cell cell = ws.Cells[0, 0];
cell.PutValue("填充"); //必须用PutValue方法赋值
cell.Style.ForegroundColor = Color.Yellow;
cell.Style.Pattern = BackgroundType.Solid;
cell.Style.Font.Size = 10;
cell.Style.Font.Color = Color.Blue;

自定义格式:

cell2
cell.Style.Custom = "ddd, dd mmmm 'yy";

旋转字体:

cell3
cell.Style.Rotation = 90;

3.设置Range并赋值加Style

range1
int styleIndex = wb.Styles.Add();
Style style = wb.Styles[styleIndex];
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
style.Font.Size = 10;

//从Cells[0,0]开始创建一个2行3列的Range
Range range = ws.Cells.CreateRange(0, 0, 2, 3);
Cell cell = range[0, 0];
cell.Style.Font = 9;
range.Style = style;
range.Merge();

注意Range不能直接设置Style.必须先定义style再将style赋给Style.其他设置和Cell基本一致.Range的Style会覆盖Cell定义的Style.另外必须先赋值再传Style.否则可能不生效.

4.使用Formula:

formula1
ws.Cells[0,0].PutValue(1);
ws.Cells[1,0].PutValue(20);
ws.Cells[2,0].Formula="SUM(A1:B1)";
wb.CalculateFormula(true);

Save Excel文件的时候必须调用CalculateFormula方法计算结果.

5.插入图片:

pictures1
string imageUrl = System.Web.HttpContext.Current.Server.MapPath("~/images/log_topleft.gif");
ws.Pictures.Add(10, 10, imageUrl);

6.使用Validations:

validations1
Cells cells = ws.Cells;

cells[12, 0].PutValue("Please enter a number other than 0 to 10 in B1 to activate data validation:");
cells[12, 0].Style.IsTextWrapped = true;

cells[12, 1].PutValue(5);
Validations validations = totalSheet.Validations;

Validation validation = validations[validations.Add()];
//Set the data validation type
validation.Type = ValidationType.WholeNumber;
//Set the operator for the data validation
validation.Operator = OperatorType.Between;
//Set the value or expression associated with the data validation
validation.Formula1 = "0";
//the value or expression associated with the second part of the data validation
validation.Formula2 = "10";

validation.ShowError = true;
//Set the validation alert style
validation.AlertStyle = ValidationAlertType.Information;
//Set the title of the data-validation error dialog box
validation.ErrorTitle = "Error";
//Set the data validation error message
validation.ErrorMessage = " Enter value between 0 to 10";
//Set the data validation input message
validation.InputMessage = "Data Validation using Condition for Numbers";
validation.IgnoreBlank = true;
validation.ShowInput = true;
validation.ShowError = true;

//设置Validations的区域，因为现在要Validations的位置是12,1，所以下面设置对应的也要是12,1
CellArea cellArea;
cellArea.StartRow = 12;
cellArea.EndRow = 12;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
validation.AreaList.Add(cellArea);

/*
要注意的地方Validations 也是和Range的Style一样，要新增的，否则不生效
*/

上边不过是基础中的基础,要了解更多就要自己不断的尝试了.下边奉上帮助文档和DLL:

点击这里下载!

附送Excel支持的56种颜色: Excel报表支持的56种颜色(Excel2003及以下版本)

http://www.cnblogs.com/KenBlove/archive/2009/01/07/1370944.html

C# winform 导出导入Excel/Doc 完整实例教程使用Aspose.Cells.dll——第一篇

http://hi.baidu.com/%BA%CE%B3%B1/blog/item/3298ae512dd15a2f42a75b1a.html

2010年01月11日星期一下午 05:32

还真没做过winform的导出导入，今天上网百度了一下。结果---

所以还是我自己写个吧。之前做过web的，半搬半做就OK。

1添加引用：Aspose.Cells.dll（我们就叫工具包吧，可以从网上下载。关于它的操作我在“Aspose.Cells操作说明中文版下载 Aspose C# 导出Excel 实例”一文中的说。这里你暂时也可不理会它。）

Aspose.Cells.dll 和中文说明下载地址： http://item.taobao.com/auction/item_detail-0db2-f9b1d99e6cb27d78ae9e6373a1529633.htm

即使没有安装office也能用噢，这是一个好强的大工具。

2编写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;
    }
}

3导出按钮事件中编写导出数据方法：

private void bt_excel_out_Click(object sender, EventArgs e)
        {

            SaveFileDialog saveFileDialog1 = new SaveFileDialog();//如果你直接从对话框中拉出来，就不用new了噢。

          //设置文件类型
            saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls|Word (*.doc)|*.doc";
            saveFileDialog1.FilterIndex = 2;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出文件保存路径";
            //saveFileDialog1.ShowDialog();
            //string strName = saveFileDialog1.FileName;

//设置默认文件类型显示顺序
//saveFileDialog1.FilterIndex = 2;

//保存对话框是否记忆上次打开的目录
saveFileDialog1.RestoreDirectory = true;

            //点了保存按钮进入
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                //获得文件路径
                string localFilePath = saveFileDialog1.FileName.ToString();

//获取文件名，不带路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("//") + 1);

//获取文件路径，不带文件名
string FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("//"));

//给文件名前加上时间
string newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;

saveFileDialog1.FileName = FilePath + "//" + newFileName;
System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile();//输出文件

                //下面是导出数据到doc       导出到excel请看下一篇（发现没有AsposeExcel
类还没有用到呢，那正是导出到excel要用的）
                StreamWriter writer = new StreamWriter(fs);
                writer.Write("tttt");//这里就是你要导出到word文档的数据
                writer.Flush();
                writer.Close();
                fs.Close();

}