Aspose.Cells 导出

http://hi.baidu.com/jiaxw/item/af2399243deb5b0876272c40

 

using Aspose.Cells;

 

 #region

    public static bool ExportToExcelNew(DataTable dt, string FileName, System.Web.HttpResponse response)
    {
        try
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.FreezePanes(1, 1, 1, 0); //冻结第一行
            sheet.Cells[0, 0].PutValue("板线");
            sheet.Cells[0, 1].PutValue("板线编号");
            sheet.Cells[0, 2].PutValue("父节点编号");
            sheet.Cells[0, 3].PutValue("识别号");
            sheet.Cells[0, 4].PutValue("集中器");
            sheet.Cells[0, 5].PutValue("小区");
            sheet.Cells[0, 6].PutValue("区段号");
            sheet.Cells[0, 7].PutValue("分局序号");
            sheet.Cells[0, 8].PutValue("户号");
            sheet.Cells[0, 9].PutValue("户名");
            sheet.Cells[0, 10].PutValue("资产编号");
            sheet.Cells[0, 11].PutValue("安装地址");
            sheet.Cells[0, 12].PutValue("地址说明");
            sheet.Cells[0, 13].PutValue("计量方式");
            sheet.Cells[0, 14].PutValue("电压等级");
            sheet.Cells[0, 15].PutValue("变压器容量");
            sheet.Cells[0, 16].PutValue("用户类型");
            sheet.Cells[0, 17].PutValue("综合倍率");
            sheet.Cells[0, 18].PutValue("电流变比");
            sheet.Cells[0, 19].PutValue("电压变比");
            sheet.Cells[0, 20].PutValue("抄表序号");
            sheet.Cells[0, 21].PutValue("备注");
            sheet.Cells[0, 22].PutValue("维护说明");
            sheet.Cells[0, 23].PutValue("负控ID");

            ///设置列1为文本，因为这列全是数字而且很长，不处理会变成自然数了。
            ///这里需要注意Style是设置风格，而StyleFlag是开关，所以即使你设置了Style，没有打开对应的StyleFlag一样没用
            Aspose.Cells.Style sc1 = workbook.Styles[workbook.Styles.Add()];
            sc1.ShrinkToFit = true;
            sc1.Number = 49;

            Aspose.Cells.StyleFlag scf1 = new Aspose.Cells.StyleFlag();
            scf1.ShrinkToFit = true;
            scf1.NumberFormat = true;
            Aspose.Cells.Column colomn1 = sheet.Cells.Columns[1];
            colomn1.ApplyStyle(sc1, scf1);

            //方式一：自动适应
            //sheet.Cells.ImportDataTable(dt, false, 1, 1);//从A2开始填充数据

            //方式二：直接逐个填充
            string PcCodeName = "", PcCode_line = "", PcCode_lineSec = "";
            bool isTrue = false; //是否第一次       
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                PcCode_line = dt.Rows[i]["PcCode_line"].ToString();
                if (isTrue == false) //第一次要提取
                {
                    PcCode_lineSec = PcCode_line;
                    isTrue = true;
                    PcCodeName = ClassFunData.GetLineNameByid(PcCode_line);
                }
                else
                {
                    if (PcCode_lineSec != PcCode_line)
                    {
                        PcCodeName = ClassFunData.GetLineNameByid(PcCode_line);
                        PcCode_lineSec = "";
                        isTrue = false;
                    }
                }

                sheet.Cells[(i + 1), 0].PutValue(PcCodeName);
                sheet.Cells[(i + 1), 1].PutValue(dt.Rows[i]["PcCode_line"].ToString());
                sheet.Cells[(i + 1), 2].PutValue(dt.Rows[i]["PcCode"].ToString());
                sheet.Cells[(i + 1), 3].PutValue(dt.Rows[i]["system_no"].ToString());
                sheet.Cells[(i + 1), 4].PutValue(dt.Rows[i]["jzq_id"].ToString());
                sheet.Cells[(i + 1), 5].PutValue(dt.Rows[i]["xiaoqu"].ToString());
                sheet.Cells[(i + 1), 6].PutValue(dt.Rows[i]["write_se"].ToString());
                sheet.Cells[(i + 1), 7].PutValue(dt.Rows[i]["order_fenju"].ToString());
                sheet.Cells[(i + 1), 8].PutValue(dt.Rows[i]["user_no"].ToString());
                sheet.Cells[(i + 1), 9].PutValue(dt.Rows[i]["user_name"].ToString());
                sheet.Cells[(i + 1), 10].PutValue(dt.Rows[i]["assets_no"].ToString());
                sheet.Cells[(i + 1), 11].PutValue(dt.Rows[i]["user_add"].ToString());
                sheet.Cells[(i + 1), 12].PutValue(dt.Rows[i]["address_info"].ToString());
                sheet.Cells[(i + 1), 13].PutValue(dt.Rows[i]["jlfs"].ToString());
                sheet.Cells[(i + 1), 14].PutValue(dt.Rows[i]["dydj"].ToString());
                sheet.Cells[(i + 1), 15].PutValue(dt.Rows[i]["byqrl"].ToString());
                sheet.Cells[(i + 1), 16].PutValue(dt.Rows[i]["byqxz"].ToString());
                sheet.Cells[(i + 1), 17].PutValue(dt.Rows[i]["bbl"].ToString());
                sheet.Cells[(i + 1), 18].PutValue(dt.Rows[i]["CT"].ToString());
                sheet.Cells[(i + 1), 19].PutValue(dt.Rows[i]["PT"].ToString());
                sheet.Cells[(i + 1), 20].PutValue(dt.Rows[i]["order_chaobiao"].ToString());
                sheet.Cells[(i + 1), 21].PutValue(dt.Rows[i]["relmark"].ToString());
                sheet.Cells[(i + 1), 22].PutValue(dt.Rows[i]["Relmark_wh"].ToString());
                sheet.Cells[(i + 1), 23].PutValue(dt.Rows[i]["Fkid"].ToString());
            }

            sheet.AutoFitColumns();//让各列自适应宽度，这个很有用。       
            workbook.Save(FileName, Aspose.Cells.FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, response);

            return true;
        }
        catch
        {
            return false;
        }

    }

    #endregion

 

DataTable dt = ClassFunData.Fun_GetLineUserDataAB("B", Pid, KeyWords).Tables[0].DefaultView.ToTable();

        String strNameFile;
        strNameFile = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
          

        bool isExport = false;

        isExport = CellToExcel.ExportToExcelNew(dt, strNameFile, Response);
        if (isExport)
        {

        }
        else
        {
            Response.Write("<script>alert('操作失败！')</script>");
        }