Esempio n. 1
0
        public void ExportToExcel(System.Data.DataTable dt)
        {
            if (dt == null)
            {
                return;
            }
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }
            Excel.Workbooks workbooks  = xlApp.Workbooks;
            Excel.Workbook  workbook   = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet  = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            Excel.Range     range      = null;
            long            totalCount = dt.Rows.Count;
            long            rowRead    = 0;
            float           percent    = 0;

            //写入标题
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Excel.Range)worksheet.Cells[1, i + 1];
                //range.Interior.ColorIndex = 15;//背景颜色
                range.Font.Bold           = true;                          //粗体
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
                //加边框
                range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                //range.ColumnWidth = 4.63;//设置列宽
                //range.EntireColumn.AutoFit();//自动调整列宽
                //r1.EntireRow.AutoFit();//自动调整行高
            }
            //写入内容
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                    range           = (Excel.Range)worksheet.Cells[r + 2, i + 1];
                    range.Font.Size = 9;//字体大小
                    //加边框
                    range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                    range.EntireColumn.AutoFit();//自动调整列宽
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                System.Windows.Forms.Application.DoEvents();
            }

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            if (dt.Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            }

            try
            {
                workbook.Saved = true;
                workbook.SaveCopyAs("C:\\datatable" + "" + ".xls");
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
            }

            xlApp.Quit();
            GC.Collect();//强行销毁
            //这个是从服务器中下载文件,(请参考我另外一个文章)
            //参考网址http://www.cnblogs.com/ghostljj/archive/2007/01/24/629293.html
            //BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"
            //    , System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000);
        }
Esempio n. 2
0
        /// <summary>
        /// 将datatable导出excel文件
        /// </summary>
        /// <param name="dt">需要导出的datatable</param>
        /// <param name="AbosultedFilePath">导出文件的绝对路径</param>
        /// <returns></returns>
        public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
        {
            dt.Columns.Remove("id");
            dt.Columns.Remove("year");
            dt.Columns.Remove("month");
            dt.Columns.Remove("jobflowid");
            dt.AcceptChanges();
            //检查数据表是否为空,如果为空,则退出
            if (dt == null)
            {
                return(false);
            }

            //创建Excel应用程序对象,如果未创建成功则退出
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel");
                return(false);
            }

            //创建Excel的工作簿
            Excel.Workbooks workbooks  = xlApp.Workbooks;
            Excel.Workbook  workbook   = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet  = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
            Excel.Range     range      = null;
            long            totalCount = dt.Rows.Count;
            long            rowRead    = 0;
            float           percent    = 0;

            range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行
            range.Merge(0);
            worksheet.Cells[1, 1]     = this.biaoti.InnerText;
            range.Font.Size           = 22;
            range.Font.Bold           = true;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.EntireColumn.AutoFit();
            range.EntireRow.AutoFit();

            //写入标题
            for (int i = 0; i < dt.Columns.Count + 1; i++)
            {
                //写入标题名称
                if (i == 0)
                {
                    worksheet.Cells[2, i + 1] = "序号"; //加入序号列
                }
                else
                {
                    worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];
                }
                range                     = (Excel.Range)worksheet.Cells[2, i + 1];
                range.Font.Bold           = true;//粗体
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Interior.ColorIndex = 15;
                range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

                if (i == 0)//序号列宽度设为自动调整
                {
                    range.EntireColumn.AutoFit();
                }
                else
                {
                    if (range.EntireColumn.ColumnWidth <= 8.5)
                    {
                        range.EntireColumn.ColumnWidth = 8.5;
                    }
                    else
                    {
                        range.EntireColumn.AutoFit();
                    }
                }
            }

            //写入DataTable中数据的内容
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count + 1; c++)
                {
                    range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
                    //写入内容
                    if (c == 0) //增加序号
                    {
                        worksheet.Cells[r + 3, c + 1] = (r + 1).ToString();
                    }
                    else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列
                    {
                        worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日");
                    }
                    else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列
                    {
                        worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
                        range.NumberFormat            = "#,##0.00";
                    }
                    else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列
                    {
                        if (dt.Rows[r][c - 1].ToString() == "1")
                        {
                            worksheet.Cells[r + 3, c + 1] = "已支付";
                        }
                        else
                        {
                            worksheet.Cells[r + 3, c + 1] = "未支付";
                        }
                    }
                    else
                    {
                        worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
                    }

                    //设置样式
                    range.Font.Size           = 9;
                    range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框

                    //设置单元格的宽度,如果小于8.5就设置为8.5,如果大于。则设置为自动
                    if (c == 0) //序号列宽度设为自动
                    {
                        range.EntireColumn.AutoFit();
                    }
                    else
                    {
                        if (range.EntireColumn.ColumnWidth <= 8.5)
                        {
                            range.EntireColumn.ColumnWidth = 8.5;
                        }
                        else
                        {
                            range.EntireColumn.AutoFit();//自动设置列宽
                        }
                    }
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                System.Windows.Forms.Application.DoEvents();
            }

            //设置合计那一行
            range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString());
            range.Font.ColorIndex = 41;
            //range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            range.Font.Size           = 10;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;                                                                       //居中
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
            range.EntireColumn.AutoFit();                                                                                                    //自动调整列宽
            worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:";                                                                                   //合计那一行的第一列
            if (dt.Rows.Count == 0)
            {
                worksheet.Cells[3 + dt.Rows.Count, 7] = 0;
            }
            else
            {
                worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")";
            }
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;

            System.Windows.Forms.Application.DoEvents();

            try
            {
                workbook.Saved = true;
                workbook.SaveCopyAs(AbosultedFilePath);
            }
            catch (Exception ex)
            {
                System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!\n" + ex.ToString());
                return(false);
            }

            workbook.Close();

            if (xlApp != null)
            {
                xlApp.Workbooks.Close();
                xlApp.Quit();

                int generation = System.GC.GetGeneration(xlApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;
                System.GC.Collect(generation);
            }

            GC.Collect();//强行销毁

            #region 强行杀死最近打开的Excel进程
            System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            System.DateTime startTime = new DateTime();
            int             m, killID = 0;
            for (m = 0; m < excelProc.Length; m++)
            {
                if (startTime < excelProc[m].StartTime)
                {
                    startTime = excelProc[m].StartTime;
                    killID    = m;
                }
            }
            if (excelProc[killID].HasExited == false)
            {
                excelProc[killID].Kill();
            }
            #endregion

            return(true);
        }
Esempio n. 3
0
        /// <summary>
        /// 将datatable导出excel文件
        /// </summary>
        /// <param name="dt">需要导出的datatable</param>
        /// <param name="AbosultedFilePath">导出文件的绝对路径</param>
        /// <returns></returns>
        public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
        {
            //检查数据表是否为空,如果为空,则退出
            if (dt == null)
            {
                return(false);
            }

            //创建Excel应用程序对象,如果未创建成功则退出
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel");
                return(false);
            }

            //创建Excel的工作簿
            Excel.Workbooks workbooks  = xlApp.Workbooks;
            Excel.Workbook  workbook   = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet  = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
            Excel.Range     range      = null;
            long            totalCount = dt.Rows.Count;
            long            rowRead    = 0;
            float           percent    = 0;

            range = (Excel.Range)worksheet.get_Range("A1", "O1");                             //获取表格中第一行
            range.Merge(0);                                                                   //合并第一行
            worksheet.Cells[1, 1]     = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题
            range.Font.Size           = 22;
            range.Font.Bold           = true;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.EntireColumn.AutoFit();
            range.EntireRow.AutoFit();

            //写入标题
            for (int i = 0; i < dt.Columns.Count + 1; i++)
            {
                //写入标题名称
                if (i == 0)
                {
                    worksheet.Cells[2, i + 1] = "序号";
                }
                else
                {
                    worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据
                }

                //设置标题的样式
                range                     = (Excel.Range)worksheet.Cells[2, i + 1];
                range.Font.Bold           = true;                                                                                                //粗体
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;                                                                       //居中
                range.Interior.ColorIndex = 15;
                range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色

                if (i == 0)                                                                                                                      //序号列宽度自动
                {
                    range.EntireColumn.AutoFit();
                }
                else
                {
                    //设置单元格的宽度,如果小于9就设置为9,如果大于。则设置为自动
                    if (range.EntireColumn.ColumnWidth <= 8.5)
                    {
                        range.EntireColumn.ColumnWidth = 8.5;
                    }
                    else
                    {
                        range.EntireColumn.AutoFit();//自动设置列宽
                    }
                }
            }

            //写入DataTable中数据的内容
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count + 1; c++)
                {
                    range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
                    //写入内容
                    if (c == 0)
                    {
                        if (r == dt.Rows.Count - 1)
                        {
                            worksheet.Cells[r + 3, c + 1] = "";
                        }
                        else
                        {
                            worksheet.Cells[r + 3, c + 1] = (r + 1).ToString();    //得到序号
                        }
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
                    }
                    else if (c == 1)
                    {
                        worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
                    }
                    else
                    {
                        worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0];
                        range.NumberFormat            = "#,##0.00";
                    }
                    //设置样式
                    range.Font.Size = 9;                                                                                                             //字体大小
                    range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框

                    //设置单元格的宽度,如果小于8.5就设置为8.5,如果大于。则设置为自动
                    if (c == 0) //序号列宽度设为自动
                    {
                        range.EntireColumn.AutoFit();
                    }
                    else
                    {
                        if (range.EntireColumn.ColumnWidth <= 8.5)
                        {
                            range.EntireColumn.ColumnWidth = 8.5;
                        }
                        else
                        {
                            range.EntireColumn.AutoFit();//自动设置列宽
                        }
                    }
                }
                if (r == dt.Rows.Count - 1)
                {
                    range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString());
                    range.Merge(0);
                    range.Value               = "合计";
                    range.Font.Size           = 9;
                    range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
                    range.EntireColumn.AutoFit();
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                System.Windows.Forms.Application.DoEvents();
            }

            try
            {
                workbook.Saved = true;
                workbook.SaveCopyAs(AbosultedFilePath);
            }
            catch (Exception ex)
            {
                System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!\n" + ex.ToString());
                return(false);
            }

            workbook.Close();

            if (xlApp != null)
            {
                xlApp.Workbooks.Close();
                xlApp.Quit();

                int generation = System.GC.GetGeneration(xlApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;
                System.GC.Collect(generation);
            }

            GC.Collect();//强行销毁

            #region 强行杀死最近打开的Excel进程
            System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            System.DateTime startTime = new DateTime();
            int             m, killID = 0;
            for (m = 0; m < excelProc.Length; m++)
            {
                if (startTime < excelProc[m].StartTime)
                {
                    startTime = excelProc[m].StartTime;
                    killID    = m;
                }
            }
            if (excelProc[killID].HasExited == false)
            {
                excelProc[killID].Kill();
            }
            #endregion

            return(true);
        }