Esempio n. 1
0
        public bool ExportReportToExcel(System.Data.DataTable dt, string fileName, Dictionary <string, object> filter)
        {
            bool succeed = false;

            if (dt != null)
            {
                try
                {
                    //Aspose.Cells.License li = new Aspose.Cells.License();
                    Aspose.Cells.Workbook  workbook  = new Aspose.Cells.Workbook();
                    Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];

                    cellSheet.Name = dt.TableName;

                    int rowIndex = 0;
                    int colIndex = 0;
                    int colCount = dt.Columns.Count;
                    int rowCount = dt.Rows.Count;
                    if (colCount >= filter.Count)
                    {
                        //列名的处理
                        foreach (KeyValuePair <string, object> de0 in filter)
                        {
                            cellSheet.Cells[rowIndex, colIndex].PutValue(de0.Key.ToString());//dt.Columns[i].ColumnName
                            Aspose.Cells.Style style = new Aspose.Cells.Style();
                            style.Font.IsBold = true;
                            style.Font.Name   = "宋体";
                            cellSheet.Cells[rowIndex, colIndex].SetStyle(style);
                            colIndex++;
                        }

                        Aspose.Cells.Style style2 = new Aspose.Cells.Style();
                        style2.Font.Name = "Arial";
                        style2.Font.Size = 10;
                        Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
                        cellSheet.Cells.ApplyStyle(style2, styleFlag);

                        rowIndex++;

                        for (int i = 0; i < rowCount; i++)
                        {
                            colIndex = 0;

                            foreach (KeyValuePair <string, object> de1 in filter)
                            {
                                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][de1.Value.ToString()].ToString());
                                colIndex++;
                            }
                            rowIndex++;
                        }
                        cellSheet.AutoFitColumns();

                        //path = System.IO.Path.GetFullPath(fileName);
                        //workbook.Save(path);

                        cellSheet.AutoFitColumns();//让各列自适应宽度,这个很有用。
                        //Response.Clear();
                        //Response.Charset = "UTF8";
                        //Response.ContentEncoding = System.Text.Encoding.UTF8;
                        //Response.HeaderEncoding = System.Text.Encoding.UTF8;
                        //Response.ContentType = "application/ms-excel";

                        HttpResponse response = Page.Response;
                        response.Clear();
                        response.ContentType = "application/octet-stream";
                        //使用UTF-8对文件名进行编码
                        fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);

                        response.ContentType = "application/ms-excel;";
                        //workbook.Save(fileName, Aspose.Cells.FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, response);
                        workbook.Save(fileName, FileFormatType.Xlsx);
                        succeed = true;
                    }
                }
                catch (Exception ex)
                {
                    succeed = false;
                }
            }

            return(succeed);
        }
Esempio n. 2
0
        public static MemoryStream ExportExcel(DataTable dt_excel, string sMapPath)
        {
            try
            {
                DirectoryInfo info = new DirectoryInfo(sMapPath);
                if (!info.Exists)
                {
                    info.Create();
                }

                Workbook doc = new Workbook();
                doc.Shared = true;
                Worksheet oSheet = doc.Worksheets[0];


                Style style = doc.Styles[doc.Styles.Add()];
                style.Font.Name     = "Arial";
                style.Font.Size     = 8;
                style.IsTextWrapped = true;
                Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
                oSheet.Cells.ApplyStyle(style, styleFlag);

                for (int i = dt_excel.Columns.Count - 1; i >= 0; i--)
                {
                    if (dt_excel.Columns[i].ColumnName.Contains("Hidden"))
                    {
                        dt_excel.Columns.RemoveAt(i);
                    }
                }

                for (int i = 0; i < dt_excel.Columns.Count; i++)
                {
                    oSheet.Cells[0, i].PutValue(dt_excel.Columns[i].ColumnName);
                    oSheet.Cells[0, i].Style.Font.IsBold = true;
                }

                Style style1 = doc.Styles[doc.Styles.Add()];                              //新增样式
                style1.HorizontalAlignment = TextAlignmentType.Center;                    //文字居中
                style1.Font.Name           = "Arial";                                     //文字字体
                style1.Font.Size           = 8;                                           //文字大小
                style1.IsLocked            = false;                                       //单元格解锁
                                                                                          //style1.Font.IsBold = true;//粗体
                style1.ForegroundColor = System.Drawing.Color.FromArgb(0x99, 0xcc, 0xff); //设置背景色
                style1.Pattern         = BackgroundType.Solid;                            //设置背景样式
                style1.IsTextWrapped   = true;                                            //单元格内容自动换行

                for (int i = 0; i < dt_excel.Rows.Count; i++)
                {
                    for (int j = 0; j < dt_excel.Columns.Count; j++)
                    {
                        if (dt_excel.Columns[j].ColumnName == "BookingDate" || dt_excel.Columns[j].ColumnName == "StatusDate")
                        {
                            oSheet.Cells[i + 1, j].PutValue(Convert.ToDateTime(dt_excel.Rows[i][j]).ToString("dd-MM-yyyy HH:mm:ss"));
                        }

                        else if (dt_excel.Columns[j].ColumnName == "WarningAlert" || dt_excel.Columns[j].ColumnName == "ConfirmationComment")
                        {
                            oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
                            oSheet.Cells[i + 1, j].SetStyle(style1);
                        }
                        //else if (dt_excel.Columns[j].ColumnName == "BookingNumber")
                        //{
                        //	oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
                        //	oSheet.Cells[i + 1, j].SetStyle(style1);
                        //}
                        else
                        {
                            oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
                        }
                    }
                }
                for (int i = 0; i < oSheet.Cells.MaxDataColumn; i++)
                {
                    if (i != 8 && i != 9)
                    {
                        oSheet.AutoFitColumn(i);
                    }
                    else
                    {
                        oSheet.Cells.SetColumnWidth(i, 50);
                    }
                }

                //oSheet.column.SetColumnWidth(9, 40);
                //oSheet.Cells.SetColumnWidth(10, 40);
                return(doc.SaveToStream());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Esempio n. 3
0
        /// <summary>
        /// 检索数据
        /// </summary>
        /// <param name="baseFilter"></param>
        /// <param name="responseEntity"></param>
        public void ExportData(BaseFilter baseFilter, ResponseEntity responseEntity)
        {
            var filter = baseFilter as RelativeDayExportDunFilter;

            if (filter == null)
            {
                ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.RequestCommandError);
                return;
            }

            DataTable dt
                = Singleton <RelativeDayDunTaskExportDAL <RelativeDaySearchDunExportViewData> > .Instance.SearchDataToDataTable(filter);

            if (dt == null || dt.Rows.Count <= 0)
            {
                ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult, "无催收单导出");
                m_Logger.Info("无催收单导出。");
                return;
            }

            AsposeXls.Workbook  workbook = new AsposeXls.Workbook();
            AsposeXls.Worksheet sheet    = workbook.Worksheets[0];

            Aspose.Cells.Style sc1 = workbook.Styles[workbook.Styles.Add()];
            sc1.ShrinkToFit         = true;
            sc1.Number              = 14;
            sc1.HorizontalAlignment = AsposeXls.TextAlignmentType.Center;
            sc1.VerticalAlignment   = AsposeXls.TextAlignmentType.Center;
            Aspose.Cells.StyleFlag scf1 = new Aspose.Cells.StyleFlag();
            scf1.ShrinkToFit         = true;
            scf1.NumberFormat        = true;
            scf1.HorizontalAlignment = true;
            scf1.VerticalAlignment   = true;
            Aspose.Cells.Column colomn1 = sheet.Cells.Columns[9];
            colomn1.ApplyStyle(sc1, scf1);

            sheet.FreezePanes(1, 1, 1, 0); //冻结第一行

            DataTable dtb = dt.Clone();

            foreach (DataRow item in dt.Rows)
            {
                DataRow NewRow = dtb.NewRow();
                for (int i = 0; i < NewRow.ItemArray.Length; i++)
                {
                    NewRow[i] = item[i];
                }
                NewRow["客户类型"] = Convert.ToByte(item["客户类型"]).ValueToDesc <EnumBusinessStatus>();
                NewRow["逾期月数"] = "M" + item["逾期月数"].ToString();
                NewRow["客户状态"] = Convert.ToByte(item["客户状态"]).ValueToDesc <EnumLawsuitStatus>();
                //NewRow["产品类型"] = Convert.ToByte(item["产品类型"]).ValueToDesc<EnumLawsuitStatus>();
                NewRow["产品种类"] = Convert.ToByte(item["产品种类"]).ValueToDesc <EnumProductKind>();
                dtb.Rows.Add(NewRow);
            }

            sheet.Cells.ImportDataTable(dtb, true, 0, 0);
            sheet.AutoFitColumns();

            byte[] result = null;
            using (MemoryStream stream = new MemoryStream())
            {
                workbook.Save(stream, SaveFormat.Xlsx);
                result = stream.ToArray();
            }

            if (result == null || result.Length == 0)
            {
                ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult);
                m_Logger.Info("无催收单导出。");
            }
            else
            {
                // 设置输出文件
                ResponseFileResult responseResult = new ResponseFileResult();
                responseResult.Result = result;

                ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.Success);
                responseEntity.Results = responseResult;
            }
        }
Esempio n. 4
0
        public static string ExportExcel(DataTable dt_excel, string sMapPath, string sFileName, Func <object, string> funConvertDateTime = null)
        {
            try
            {
                DirectoryInfo info = new DirectoryInfo(sMapPath);
                if (!info.Exists)
                {
                    info.Create();
                }

                string sFullPath = sMapPath + sFileName;

                Workbook doc = new Workbook();
                doc.Shared = true;
                Worksheet oSheet = doc.Worksheets[0];

                oSheet.Cells.SetColumnWidth(0, 30);

                Style style = doc.Styles[doc.Styles.Add()];
                style.Font.Name     = "Arial";
                style.Font.Size     = 8;
                style.IsTextWrapped = true;
                Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
                oSheet.Cells.ApplyStyle(style, styleFlag);

                for (int i = dt_excel.Columns.Count - 1; i >= 0; i--)
                {
                    if (dt_excel.Columns[i].ColumnName.Contains("Hidden"))
                    {
                        dt_excel.Columns.RemoveAt(i);
                    }
                }

                for (int i = 0; i < dt_excel.Columns.Count; i++)
                {
                    oSheet.Cells[0, i].PutValue(dt_excel.Columns[i].ColumnName);
                    oSheet.Cells[0, i].Style.Font.IsBold = true;
                }

                for (int i = 0; i < dt_excel.Rows.Count; i++)
                {
                    for (int j = 0; j < dt_excel.Columns.Count; j++)
                    {
                        oSheet.Cells.SetColumnWidth(j, 40);
                        if (dt_excel.Columns[j].DataType == typeof(DateTime) && funConvertDateTime != null)
                        {
                            oSheet.Cells[i + 1, j].PutValue(funConvertDateTime(dt_excel.Rows[i][j]));
                        }
                        else
                        {
                            oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
                        }
                    }
                }
                oSheet.AutoFitColumns();
                doc.Save(sFullPath);
                return(sFullPath);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Esempio n. 5
0
        private void btn导出_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count < 1)
            {
                return;
            }
            Workbook work = new Aspose.Cells.Workbook();
            //Worksheet sheet = work.Worksheets["New Worksheet1"];
            Worksheet sheet = default(Aspose.Cells.Worksheet);

            Aspose.Cells.Style style = new Aspose.Cells.Style();
            style.Font.Name = "宋体";
            Aspose.Cells.Style stylecolor = new Aspose.Cells.Style();
            stylecolor.ForegroundColor = Color.LightBlue;
            Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
            flag.Font = true;


            //

            sheet              = work.Worksheets[0];
            intExcelTempIndex += 1;
            sheet.Name         = "使用记录" + intExcelTempIndex.ToString();

            int Rowi    = 0;
            int Rown    = 0;
            int RownMax = 0;

            //最后Export列不导出
            RownMax = System.Convert.ToInt32(dataGridView1.Columns.Count - 0);
            ArrayList colList = new ArrayList(); //存放不显示的列
            int       Col     = 0;

            for (int i = 0; i < RownMax; i++)
            {
                if (dataGridView1.Columns[i].Width <= 5 || dataGridView1.Columns[i].Visible == false)
                {
                }
                else
                {
                    colList.Add(i);
                    sheet.Cells[0, Col].PutValue(dataGridView1.Columns[i].HeaderText);
                    Col++;
                }
            }
            //表内容
            int intcount = 0;

            for (Rowi = 0; Rowi <= dataGridView1.Rows.Count - 1; Rowi++)
            {
                if (dataGridView1.Rows[Rowi].Visible == false)
                {
                    intcount++;
                    continue;
                }
                for (Rown = 0; Rown <= colList.Count - 1; Rown++)
                {
                    if (dataGridView1.Rows[Rowi].Cells[Rown].Value != null)
                    {
                        sheet.Cells[Rowi + 1 - intcount, Rown].PutValue(dataGridView1.Rows[Rowi].Cells[Rown].Value.ToString());
                    }
                    else
                    {
                        sheet.Cells[Rowi + 1 - intcount, Rown].PutValue("");
                    }
                }
            }
            //reportToexcel();
            string filepath = "";

            sheet.Cells.ApplyStyle(style, flag);
            if (dataGridView1.Rows.Count > 60000)
            {
                filepath = System.Windows.Forms.Application.StartupPath + "\\" + sheet.Name + ".xlsx";
            }
            else
            {
                filepath = System.Windows.Forms.Application.StartupPath + "\\" + sheet.Name + ".xls";
            }
            work.Save(filepath);
            System.Diagnostics.Process.Start(filepath); //'打开导出Excel
        }