使用Aspose.Cells实现后台输出Excel报表（C#）

使用Aspose.Cells实现后台输出Excel报表

 这项任务的需求简单明了，将前期在网页中的报表作为Excel工作簿输出。其中包含较多的sheet工作表,需要设置较复杂的颜色和格式。

1.获得想要输出的DataTable

     DataTable tempdatatable = new DataTable();     try {                    //获取DataTable数据                    tempdatatable = GetDataTable(date, cities, paramlist[i], "是");     }     catch (Exception e)     {      Logger.Error(e);     }

2.初始化Workbook

Workbook workbook = new Workbook();            //标题样式            Style titlestyle = workbook.Styles[workbook.Styles.Add()];//新增样式            titlestyle.ForegroundColor = Color.FromArgb(241, 241, 241);            titlestyle.Pattern = BackgroundType.Solid;            titlestyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中            titlestyle.VerticalAlignment = TextAlignmentType.Center;//上下居中            //titlestyle.Font.Name = "宋体";//文字字体            titlestyle.Font.Size = 10;//文字大小            titlestyle.Font.IsBold = true;//粗体            titlestyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;            titlestyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;            titlestyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;            titlestyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;            titlestyle.Borders.SetColor(Color.FromArgb(160, 161, 161));            titlestyle.IsTextWrapped = true;            //内容样式            Style contentstyle = workbook.Styles[workbook.Styles.Add()];            //contentstyle.Font.Name = "宋体";//文字字体            contentstyle.Font.Size = 10;//文字大小            contentstyle.Font.IsBold = true;//粗体            contentstyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中            contentstyle.VerticalAlignment = TextAlignmentType.Center;//上下居中            contentstyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;            contentstyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;            contentstyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;            contentstyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;            contentstyle.Borders.SetColor(Color.FromArgb(160, 161, 161));            //告警样式——红色背景            Style contenredtstyle = workbook.Styles[workbook.Styles.Add()];            //contenredtstyle.Font.Name = "宋体";//文字字体            contenredtstyle.Font.Size = 10;//文字大小            contenredtstyle.Font.IsBold = true;//粗体            contenredtstyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中            contenredtstyle.VerticalAlignment = TextAlignmentType.Center;//上下居中            contenredtstyle.ForegroundColor = Color.FromArgb(230, 32, 32);            contenredtstyle.Pattern = BackgroundType.Solid;            //绿色字体            Style topthreegreen = workbook.Styles[workbook.Styles.Add()];            topthreegreen.Font.Color = Color.FromArgb(0, 255, 0);            //topthreegreen.Font.Name = "宋体";//文字字体            topthreegreen.Font.Size = 10;//文字大小            topthreegreen.Font.IsBold = true;//粗体            topthreegreen.HorizontalAlignment = TextAlignmentType.Center;//文字居中            topthreegreen.VerticalAlignment = TextAlignmentType.Center;//上下居中            //红色字体            Style redfont = workbook.Styles[workbook.Styles.Add()];            redfont.Font.Color = Color.Red;            redfont.Font.Size = 10;//文字大小            redfont.Font.IsBold = true;//粗体            redfont.HorizontalAlignment = TextAlignmentType.Center;//文字居中            redfont.VerticalAlignment = TextAlignmentType.Center;//上下居中

3.填充数据并设置格式

            string filePathBase = ConfigurationManager.AppSettings["File_Path"].ToString() + "\\";            try            {                for (int i = 0; i < paramlist.Length; i++)            {                DataTable tempdatatable = new DataTable();                try                {                    //获取DataTable数据                    tempdatatable = GetDataTable(date, cities, paramlist[i], "是");                }                catch (Exception e)                {                    Logger.Error(e);                }                if (i > 0)                {                    workbook.Worksheets.Add();                }                Worksheet worksheet = workbook.Worksheets[i];//                worksheet.Name = paramlist[i];                Cells cells = worksheet.Cells;                int Colnum = tempdatatable.Columns.Count;//表格列数                int Rownum = tempdatatable.Rows.Count;//表格行数                 if (Colnum > 0)                {                    //设置列头                    for (int c = 0; c < Colnum; c++)                    {                        cells[0, c].PutValue(tempdatatable.Columns[c].ColumnName);                        cells[0, c].SetStyle(titlestyle);                        cells.SetColumnWidth(c, 15);                        cells.SetRowHeight(0, 25);                    }                    //填入数据 设置style                    for (int r = 0; r < Rownum; r++)                    {                        object lastmonthvalue = tempdatatable.Rows[r][1];                        object tmpvalue = null;                        cells.SetRowHeight(r + 1, 15);                        for (int c = 0; c < Colnum; c++)                        {                            tmpvalue = tempdatatable.Rows[r][c];                            cells[r + 1, c].PutValue(tmpvalue.ToString());                            cells[r + 1, c].SetStyle(contentstyle);                            if (c >= 4 && r > 1 && r < 16)//只选择第四列之后，第三行与第十六行之间的数据进行门限比对                            {                                bool iscellerror = CheckCellValue(paramlist[i], lastmonthvalue, tmpvalue);                                if (iscellerror)                                {//达到标红门限                                    cells[r + 1, c].SetStyle(contenredtstyle);                                }                            }                            if (c == 3 && r > 1 && r < 15)//排名                            {                                int ranking = Convert.ToInt32(tmpvalue);                                if (ranking <= 3)                                {                                    cells[r + 1, c].SetStyle(topthreegreen);                                }                                if (ranking >= 11)                                {                                    cells[r + 1, c].SetStyle(contenredtstyle);                                }                            }                        }                    }                    //列头2 3                    for (int c = 0; c < Colnum; c++)                    {                        cells[1, c].SetStyle(titlestyle);                        cells[2, c].SetStyle(titlestyle);                    }                    cells[18, 0].SetStyle(redfont);//恶化原因说明                    worksheet.FreezePanes(1, 4, 1, 4);//冻结前四列 冻结前三行                }                //setColumnWithAuto(worksheet);自动列宽            }            }            catch (Exception ex)            {                Logger.Error(ex);                excelFileInfo.setError("出错", ex.Message, -1);            }

4.输出文件

 if (excelFileInfo.Data != (object)(-1)){    string filename = "网络日报问题跟踪\\网络日报问题跟踪表" + date + ".xlsx";    if (!Directory.Exists(filePathBase + "网络日报问题跟踪"))    {    // Create the directory it does not exist.    Directory.CreateDirectory(filePathBase + "网络日报问题跟踪");    }    workbook.Save(filePathBase + filename);    excelFileInfo.setInfo("", filename);}return excelFileInfo;

*设置自动列宽的方法

     void setColumnWithAuto(Worksheet sheet)        {            Cells cells = sheet.Cells;            int columnCount = cells.MaxColumn;  //获取表页的最大列数            int rowCount = cells.MaxRow;        //获取表页的最大行数            for (int col = 0; col < columnCount; col++)            {                sheet.AutoFitColumn(col, 0, rowCount);            }            for (int col = 0; col < columnCount; col++)            {                cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);            }        }

*其他高难度内容可参考 http://www.cnblogs.com/wuhuacong/archive/2013/01/04/2844310.html

最终实现输出的文档