コード例 #1
0
ファイル: Form2.cs プロジェクト: haixingdev/toos
        private void creat_Click(object sender, EventArgs e)
        {
            // textBox2.Text = UInt96.Parse(textBox1.Text).Inc().ToString();
            saveFileDialog1.Title    = "MAC地址生成工具----百思威科技";
            saveFileDialog1.Filter   = "Excel(*.xls)|*.xls";
            saveFileDialog1.FileName = string.Format("MAC地址_{0}", DateTime.Now.ToString("yyyyMMdd"));
            DialogResult result = saveFileDialog1.ShowDialog();

            Excel._Application xlapp   = new Excel.Application();
            Excel.Workbook     xlbook  = xlapp.Workbooks.Add(true);
            Excel.Worksheet    xlsheet = (Excel.Worksheet)xlbook.Worksheets[1];
            //  MessageBox.Show(textBox2.Text);
            int    RowCount = Convert.ToInt32(textBox2.Text);
            int    RowIndex = 0;
            string val      = textBox1.Text;

            val = val.Replace(":", "");
            for (int i = 0; i < RowCount; i++)
            {
                RowIndex++;
                if (i != 0)
                {
                    if (radioButton1.Checked)
                    {
                        val = UInt96.Parse(val).Inc().ToString(":");
                    }
                    else
                    {
                        val = UInt96.Parse(val).Inc().ToString("");
                    }
                    xlsheet.Cells[RowIndex, 1] = val;
                }
                else
                {
                    if (radioButton1.Checked)
                    {
                        string[]      sArray   = Regex.Split(val, @"(\w{2})");
                        List <string> listTemp = new List <string>();
                        foreach (string s in sArray)
                        {
                            if (string.IsNullOrEmpty(s))
                            {
                                continue;
                            }
                            listTemp.Add(s);
                        }
                        string[] newlist = listTemp.ToArray();
                        val = String.Join(":", newlist);
                    }
                    xlsheet.Cells[RowIndex, 1] = val;
                }
            }
            xlbook.Saved = true;
            xlbook.SaveCopyAs(saveFileDialog1.FileName);
            xlapp.Quit();
            MessageBox.Show("导出成功!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
コード例 #2
0
ファイル: ExcelInput.cs プロジェクト: wwkkww1983/bt
        /// <summary>
        ///
        /// </summary>
        private void InputExcel()
        {
            //			if(m_dt.Rows.Count==0)
            //			else
            //			{
            //				m_exl=new Excel.ApplicationClass();
            //				m_exl.Workbooks.Add ( true );
            //				for(int j=0;j<m_dt.Columns.Count;j++)
            //				{
            //					for(int z=0;z<m_Title.Length;z++)
            //					{
            //						if(m_dt.Columns[j].ColumnName==m_Title[z].name)
            //						{
            //							m_exl.Cells[2,j+1]=m_Title[z].title;
            //							for(int i=0;i<m_dt.Rows.Count;i++)
            //							{
            //								m_exl.Cells[i+3,j+1]=m_dt.Rows[i][j];
            //							}
            //							break;
            //						}
            //
            //					}
            //					continue;
            //				}
            //				m_exl.Visible=true;
            //			}
            //			else
            //			{
            string str = Path.GetDirectoryName(Application.ExecutablePath) + "\\report\\GRDB.xls";

            m_exl = new Excel.ApplicationClass();
            //				m_exl.Workbooks.Add(true);
            Excel.Workbook eWork = m_exl.Workbooks.Add(str);          //true)


            eWork.SaveCopyAs("ll");
            m_exl.Cells[2, 1] = m_time;
            for (int i = 1; i < m_Title.Length; i++)
            {
                for (int j = 0; j < m_dt.Columns.Count; j++)
                {
                    if (m_dt.Columns[j].ColumnName == m_Title[i].name)
                    {
                        //							m_exl.Cells[2,i]=m_Title[i].title;
                        for (int z = 0; z < m_dt.Rows.Count; z++)
                        {
                            m_exl.Cells[z + 5, i] = m_dt.Rows[z][j];
                        }
                    }
                }
            }
            m_exl.Visible = true;

            //			}
        }
コード例 #3
0
ファイル: frmBase.cs プロジェクト: zhenghua75/CMSM
        protected void BusiIncomeExportToExcel(string tabname, string tabdate, DataTable dtIncome)
        {
            try
            {
                Excel.Application xapp   = new Excel.ApplicationClass();
                Excel.Workbook    xbook  = xapp.Workbooks.Open(Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Worksheet   xSheet = (Excel.Worksheet)xbook.Sheets["业务量"];              //得到Sheet

                xSheet.get_Range("A1", Missing.Value).Value2 = tabname;
                xSheet.get_Range("A2", Missing.Value).Value2 = tabdate;
                for (int i = 1; i < dtIncome.Rows.Count - 2; i++)
                {
                    for (int j = 1; j < 8; j++)
                    {
                        xSheet.Cells[i + 3, j + 1] = dtIncome.Rows[i][j].ToString();
                    }
                }

                for (int i = 1; i < 8; i++)
                {
                    xSheet.Cells[21, i + 1] = dtIncome.Rows[18][i].ToString();
                }

                SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
                SaveFileDialog1.Filter   = "Excel文件(*.xls)|*.xls";
                SaveFileDialog1.FileName = SysInitial.CP + "业务量报表" + DateTime.Now.ToShortDateString() + ".xls";
                if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    xbook.SaveCopyAs(SaveFileDialog1.FileName);                                           //另存
                    xbook.Close(false, Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value); //关闭
                    xSheet = null;
                    xbook  = null;
                    xapp.Quit();
                    xapp = null;
                }
                else
                {
                    xbook.Close(false, Missing.Value, Missing.Value);                    //关闭
                    xSheet = null;
                    xbook  = null;
                    xapp.Quit();
                    xapp = null;
                }
            }
            catch (Exception err)
            {
                MessageBox.Show("导出时出错,请重试!", "系统提示", MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                clog.WriteLine(err);
            }
            finally
            {
            }
        }
コード例 #4
0
ファイル: InitConnect.cs プロジェクト: a1264393659/book
 /// <summary>
 /// 导出数据
 /// </summary>
 /// <param name="selectstring">选择导出数据的SQL选择语句</param>
 public static void DataOut(SaveFileDialog dialog, string selectstring)
 {
     if (dialog.ShowDialog() == DialogResult.OK)
     {
         Excel.ApplicationClass excel     = new Excel.ApplicationClass();
         Excel.Workbook         workbook  = excel.Workbooks.Add(System.Reflection.Missing.Value);
         Excel.Worksheet        worksheet = (Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
         worksheet.Cells.NumberFormatLocal = "@";
         ///////////////////////////////////
         SqlConnection connect = InitConnect.GetConnection();
         SqlDataReader read    = null;
         try
         {
             connect.Open();
             SqlCommand cmd = new SqlCommand(selectstring, connect);
             read = cmd.ExecuteReader();
             for (int i = 0; i < read.FieldCount; i++)
             {
                 worksheet.Cells[1, i + 1] = read.GetName(i).Trim();
             }
             int row   = 2;
             int count = 0;
             while (read.Read())
             {
                 count++;
                 for (int i = 0; i < read.FieldCount; i++)
                 {
                     worksheet.Cells[row, i + 1] = read[i].ToString().Trim();
                 }
                 row++;
             }
             MessageBox.Show("成功导出" + count.ToString() + "条记录!", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
         }
         catch (Exception ee)
         {
             MessageBox.Show("错误:" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
         }
         finally
         {
             read.Close();
             connect.Close();
             object change = false, filename = dialog.FileName;
             workbook.SaveCopyAs(filename);
             workbook.Close(change, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
             excel.Quit();
         }
     }
 }
コード例 #5
0
ファイル: Form1.cs プロジェクト: lastmandancing88/SANY
        /// <summary>
        /// 将DataTable中的列名及数据导出到Excel表中
        /// </summary>
        /// <param name="tmpDataTable">要导出的DataTable</param>
        /// <param name="strFileName">Excel的保存路径及名称</param>
        public void DataTabletoExcelkk(System.Data.DataTable tmpDataTable, string strFileName)
        {
            if (tmpDataTable == null)
            {
                return;
            }
            int rowNum      = tmpDataTable.Rows.Count;
            int columnNum   = tmpDataTable.Columns.Count;
            int rowIndex    = 1;
            int columnIndex = 0;

            Excel.Application xlApp = new Excel.ApplicationClass();
            xlApp.DefaultFilePath     = "";
            xlApp.DisplayAlerts       = true;
            xlApp.SheetsInNewWorkbook = 1;
            Excel.Workbook  xlBook = xlApp.Workbooks.Add(true);
            Excel.Worksheet ws     = (Excel.Worksheet)xlBook.Worksheets[1];
            int             colnum = tmpDataTable.Columns.Count;

            Excel.Range r         = ws.get_Range("A1", NumToExeclRowStr(colnum) + "1");
            object[]    objHeader = new object[colnum];

            //将DataTable的列名导入Excel表第一行
            foreach (DataColumn dc in tmpDataTable.Columns)
            {
                objHeader[columnIndex] = dc.ColumnName;
                columnIndex++;
            }
            r.Value2 = objHeader;

            //将DataTable中的数据导入Excel中
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    objHeader[columnIndex] = tmpDataTable.Rows[i][j].ToString();
                    columnIndex++;
                }
                r        = ws.get_Range("A" + (i + 2), NumToExeclRowStr(colnum) + (i + 2));
                r.Value2 = objHeader;
            }
            r.EntireColumn.AutoFit();
            xlBook.SaveCopyAs(strFileName);
        }
コード例 #6
0
ファイル: excel_operator.cs プロジェクト: qxuan521/Coding
 private static void excelSaveClose(string szPath, string szResultPath, Excel.Application rExcel, Excel.Workbook rWorkbook)
 {
     Process[] localByNameApp = Process.GetProcessesByName(szPath);//获取程序名的所有进程
     if (localByNameApp.Length > 0)
     {
         foreach (var app in localByNameApp)
         {
             //                  if (!app.HasExited)
             //                  {
             #region
             ////设置禁止弹出保存和覆盖的询问提示框
             rExcel.DisplayAlerts          = false;
             rExcel.AlertBeforeOverwriting = false;
             rExcel.Visible = false;
             //wb.Saved = true;
             ////保存工作簿
             //rExcel.Application.Workbooks.Add(true).Save();
             //保存excel文件
             ///excel.Save("E:\\c#_test\\winFormTest\\winFormTest\\hahaha.xls");
             //确保Excel进程关闭
             rExcel.Quit();
             rExcel = null;
             #endregion
             app.Kill();//关闭进程
             /*}*/
         }
     }
     if (rWorkbook != null)
     {
         rExcel.DisplayAlerts          = false;
         rExcel.AlertBeforeOverwriting = false;
         rExcel.Visible = false;
         //rExcel.Application.Workbooks.Add(true).Save();
         //保存结果
         rWorkbook.SaveCopyAs(szResultPath);
         ///*, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing*/);
         //wb.Save();
         rWorkbook.Close(false, szPath, szPath);
     }
     rExcel.Quit();
     // 安全回收进程
     System.GC.GetGeneration(rExcel);
 }
コード例 #7
0
ファイル: 生产记录查询.cs プロジェクト: fengyubox/SFIS
        /// <summary>
        /// 将DataTable中的列名及数据导出到Excel表中
        /// </summary>
        /// <param name="tmpDataTable">要导出的DataTable</param>
        /// <param name="strFileName">Excel的保存路径及名称</param>
        public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {
            if (tmpDataTable == null)
            {
                return;
            }
            int rowNum      = tmpDataTable.Rows.Count;
            int columnNum   = tmpDataTable.Columns.Count;
            int rowIndex    = 1;
            int columnIndex = 0;

            Excel.Application xlApp = new Excel.ApplicationClass();
            xlApp.DefaultFilePath     = "";
            xlApp.DisplayAlerts       = true;
            xlApp.SheetsInNewWorkbook = 1;
            Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

            //将DataTable的列名导入Excel表第一行
            foreach (DataColumn dc in tmpDataTable.Columns)
            {
                columnIndex++;
                xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
            }

            //将DataTable中的数据导入Excel中
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
                }
            }
            xlBook.SaveCopyAs(strFileName);
        }
コード例 #8
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);
        }
コード例 #9
0
ファイル: FrmQueryKss.cs プロジェクト: Wooyme/HIS-1
        protected void ExportExcel(DataTable dt, string table)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return;
            }
            Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                return;
            }
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook  workbook  = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
            //Excel.Workbook workbook = xlApp.Workbooks.Open(table, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet; //第一个sheet页
            worksheet.Name = "武汉市公费"; //这里修改sheet名称

            try
            {
                Excel.Range range;
                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.NumberFormat = "0.00";
                }

                DataTable dtDec = DecCol();

                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string  strValue = dt.Rows[r][i].ToString();
                        decimal dm       = 0M;
                        if (dtDec.Columns.Contains(dt.Columns[i].ColumnName))
                        {
                            ((Excel.Range)worksheet.Cells[r + 2, i + 1]).NumberFormat = "0.00";
                            worksheet.Cells[r + 2, i + 1] = strValue;
                        }
                        else
                        {
                            worksheet.Cells[r + 2, i + 1] = "'" + strValue;
                            //worksheet.Cells[r + 2, i + 1] = strValue;
                        }
                    }
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                }
                xlApp.Visible = true;
            }
            catch (Exception ex)
            {
            }
            finally
            {
                workbook.Saved = true;

                if (System.IO.File.Exists(table))
                {
                    System.IO.File.Delete(table);
                }
                workbook.SaveCopyAs(table);
                workbook.Close(true, Type.Missing, Type.Missing);
                workbook = null;
                xlApp.Quit();
                xlApp = null;
            }
        }
コード例 #10
0
ファイル: 诊疗卡收入统计.cs プロジェクト: Wooyme/HIS-1
        private void Save()
        {
            try
            {
                this.Cursor = PubStaticFun.WaitCursor();
                #region 简单打印
                DataTable tb = (DataTable)this.dataGridViewX1.DataSource;

                //if (tb == null || tb.Rows.Count == 0)
                //{
                //    return;
                //}



                Excel.Application myExcel = new Excel.ApplicationClass(); //new Excel.Application();
                //关闭时不显示提示
                //myExcel.DefaultFilePath = "";
                //myExcel.DisplayAlerts = false;
                //myExcel.SheetsInNewWorkbook = 1;
                Excel.Workbook xlBook = myExcel.Application.Workbooks.Add(true);

                //查询条件
                string rq = "日期从:" + dtpjsrq1.Value.ToString() + " 到:" + dtpjsrq2.Value.ToString() + "   收费员:" + this.comboBox1.Text;
                string ks = "";

                string swhere = rq + ks;


                //写入行头

                int SumRowCount = tb.Rows.Count;
                int SumColCount = 0;

                for (int j = 0; j < this.dataGridViewX1.Columns.Count; j++)
                {
                    if (this.dataGridViewX1.Columns[j].Visible)
                    {
                        SumColCount = SumColCount + 1;
                        myExcel.Cells[5, SumColCount] = "" + this.dataGridViewX1.Columns[j].HeaderText;
                    }
                }
                myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true;
                myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10;


                //逐行写入数据,

                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int ncol = 0;
                    for (int j = 0; j < tb.Columns.Count; j++)
                    {
                        if (this.dataGridViewX1.Columns[j].Visible)
                        {
                            ncol = ncol + 1;
                            myExcel.Cells[6 + i, ncol] = "'" + tb.Rows[i][j].ToString().Trim();
                            //if (tb.Columns[j].ColumnName.IndexOf("比例") >= 0)
                            //{
                            //    myExcel.Cells[6 + i, ncol] = "'" + Convert.ToDecimal(Convert.ToDecimal(tb.Rows[i][j].ToString().Trim()) * 100).ToString("0.00") + "%";
                            //}
                        }
                    }
                }

                //设置报表表格为最适应宽度
                myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select();          //6
                myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //6

                //加边框
                myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1;

                //报表名称
                string ss = TrasenFrame.Classes.Constant.HospitalName + "门诊预交金收支情况统计";
                myExcel.Cells[1, 1] = ss;
                myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true;
                myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16;
                //报表名称跨行居中
                myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select();
                myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

                //报表条件
                myExcel.Cells[3, 1] = swhere.Trim();
                myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10;
                myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select();
                myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //最后一行为黄色
                myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19;

                xlBook.SaveCopyAs(Application.StartupPath + "\\门诊预交金收支情况统计.xls");
                //让Excel文件可见
                myExcel.Visible = false;

                //

                //打印



                //myExcel.Workbooks.Close();
                if (xlBook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
                    xlBook = null;
                }
                if (myExcel != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                    myExcel = null;
                    //xlApp.Quit();
                }
                GC.Collect();

                #endregion
            }
            catch (System.Exception err)
            {
                MessageBox.Show(err.Message + "错误提示");
            }
            finally
            {
                //this.btExcel.Enabled = true;
                this.Cursor = Cursors.Arrow;
            }
        }
コード例 #11
0
        private void OutPut()
        {
            Excel.Application objApp   = new Excel.ApplicationClass();
            Excel.Workbooks   objbooks = objApp.Workbooks;
            Excel.Workbook    objbook  = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet   objSheet = (Excel.Worksheet)objbook.Worksheets[1];//取得sheet1
            Excel.Range       range;
            string            filename = "";

            try
            {
                //生成.xls文件完整路径名
                filename = Server.MapPath("/RailExamBao/Excel/CompuerServerCount.xls");

                if (File.Exists(filename.ToString()))
                {
                    File.Delete(filename.ToString());
                }

                //将所得到的表的列名,赋值给单元格

                objSheet.Cells[1, 1] = "站段名称";

                objSheet.Cells[1, 2] = "服务器名称";
                range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]);
                range.Merge(0);

                objSheet.Cells[1, 5] = "使用人次";

                objSheet.Cells[1, 6] = "其他单位使用人次";

                objSheet.Cells[1, 7] = "其他单位使用天数";

                DataSet ds = (DataSet)ViewState["Grid"];

                int i = 0;
                //同样方法处理数据
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    objSheet.Cells[2 + i, 1] = dr["Short_Name"].ToString();

                    objSheet.Cells[2 + i, 2] = dr["Computer_Server_Name"].ToString();
                    range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]);
                    range.Merge(0);

                    objSheet.Cells[2 + i, 5] = dr["使用人次"].ToString();

                    objSheet.Cells[2 + i, 6] = dr["其他单位使用人次"].ToString();

                    objSheet.Cells[2 + i, 7] = dr["其他单位使用天数"].ToString();

                    i++;
                }

                //不可见,即后台处理
                objApp.Visible = false;

                objbook.Saved = true;
                objbook.SaveCopyAs(filename);
            }
            catch
            {
                SessionSet.PageMessage = "系统错误,导出Excel文件失败!";
            }
            finally
            {
                objbook.Close(Type.Missing, filename, Type.Missing);
                objbooks.Close();
                objApp.Application.Workbooks.Close();
                objApp.Application.Quit();
                objApp.Quit();
                GC.Collect();
            }
        }
コード例 #12
0
        private void OutPut()
        {
            RandomExamCountStatisticBLL objBll = new RandomExamCountStatisticBLL();
            //I当前登录人ID
            int _OrgId = PrjPub.CurrentLoginUser.StationOrgID;
            //等1:路局 等于0:站段
            int _SuitRangeId = PrjPub.CurrentLoginUser.SuitRange;
            //考试开始时间
            DateTime _DateFrom = Convert.ToDateTime(dateStartDateTime.DateValue);
            //考试结束时间
            DateTime _DateTo = Convert.ToDateTime(dateEndDateTime.DateValue);
            int      style   = Convert.ToInt32(ddlStyle.SelectedValue);
            IList <RailExam.Model.RandomExamCountStatistic> objList = objBll.GetCountWithOrg(_SuitRangeId, _OrgId, _DateFrom, _DateTo, PrjPub.GetRailSystemId(), style);


            Excel.Application objApp   = new Excel.ApplicationClass();
            Excel.Workbooks   objbooks = objApp.Workbooks;
            Excel.Workbook    objbook  = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet   objSheet = (Excel.Worksheet)objbook.Worksheets[1];          //取得sheet1
            Excel.Range       range;
            string            filename = "";

            try
            {
                //生成.xls文件完整路径名
                filename = Server.MapPath("/RailExamBao/Excel/Count.xls");

                if (File.Exists(filename.ToString()))
                {
                    File.Delete(filename.ToString());
                }

                //将所得到的表的列名,赋值给单元格

                objSheet.Cells[1, 1] = "序号";

                objSheet.Cells[1, 2] = "站段单位";
                range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]);
                range.Merge(0);

                objSheet.Cells[1, 5] = "考试次数";

                objSheet.Cells[1, 6] = "参考人次";

                //同样方法处理数据
                for (int i = 0; i < objList.Count; i++)
                {
                    objSheet.Cells[2 + i, 1] = i + 1;

                    objSheet.Cells[2 + i, 2] = objList[i].OrgName;
                    range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]);
                    range.Merge(0);

                    objSheet.Cells[2 + i, 5] = objList[i].ExamCount;

                    objSheet.Cells[2 + i, 6] = objList[i].EmployeeCount;
                }

                //不可见,即后台处理
                objApp.Visible = false;

                objbook.Saved = true;
                objbook.SaveCopyAs(filename);
            }
            catch
            {
                SessionSet.PageMessage = "系统错误,导出Excel文件失败!";
            }
            finally
            {
                objbook.Close(Type.Missing, filename, Type.Missing);
                objbooks.Close();
                objApp.Application.Workbooks.Close();
                objApp.Application.Quit();
                objApp.Quit();
                GC.Collect();
            }
        }
コード例 #13
0
        private void ExportArrange()
        {
            // 根据 ProgressBar.htm 显示进度条界面
            string       templateFileName = Path.Combine(Server.MapPath("."), "ProgressBar.htm");
            StreamReader reader           = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
            string       html             = reader.ReadToEnd();

            reader.Close();
            Response.Write(html);
            Response.Flush();
            System.Threading.Thread.Sleep(200);

            string strExamId = Request.QueryString.Get("id");

            RandomExamArrangeBLL eaBll = new RandomExamArrangeBLL();
            IList <RailExam.Model.RandomExamArrange> ExamArranges = eaBll.GetRandomExamArranges(int.Parse(strExamId));

            RandomExamBLL objBll = new RandomExamBLL();

            RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("id")));

            EmployeeBLL psBLL = new EmployeeBLL();
            DataSet     ds    = new DataSet();

            string[]         str     = ExamArranges[0].UserIds.Split(',');
            IList <Employee> objList = new List <Employee>();

            if (str[0] != "")
            {
                OracleAccess db = new OracleAccess();
                string       strSql;

                OrganizationBLL orgBll = new OrganizationBLL();
                for (int i = 0; i < str.Length; i++)
                {
                    Employee obj = psBLL.GetChooseEmployeeInfo(str[i]);
                    obj.RowNum = i + 1;

                    if (PrjPub.CurrentLoginUser.RoleID != 1)
                    {
                        if (orgBll.GetStationOrgID(obj.OrgID) == PrjPub.CurrentLoginUser.StationOrgID)
                        {
                            objList.Add(obj);
                        }
                    }
                    else
                    {
                        objList.Add(obj);
                    }
                }

                if (objList.Count > 0)
                {
                    ds.Tables.Add(ConvertToDataTable((IList)objList));

                    if (ViewState["Sort"] != null)
                    {
                        ds.Tables[0].DefaultView.Sort = ViewState["Sort"].ToString();
                    }

                    DataColumn dc = ds.Tables[0].Columns.Add("ComputeRoom");

                    strSql = "select a.*,c.Short_Name||'-'||b.Computer_Room_Name as ComputeRoom "
                             + " from Random_Exam_Arrange_Detail a "
                             + " inner join Computer_Room b on a.Computer_Room_ID=b.Computer_Room_ID"
                             + " inner join Org c on b.Org_ID=c.Org_ID"
                             + " where Random_Exam_ID='" + strExamId + "'";
                    DataSet dsDetail = db.RunSqlDataSet(strSql);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        string    strUser = "******" + dr["EmployeeID"] + ",";
                        DataRow[] drs     = dsDetail.Tables[0].Select("','+User_Ids+',' like '%" + strUser + "%'");

                        if (drs.Length > 0)
                        {
                            dr["ComputeRoom"] = drs[0]["ComputeRoom"].ToString();
                        }
                        else
                        {
                            dr["ComputeRoom"] = string.Empty;
                        }
                    }
                }

                System.Threading.Thread.Sleep(10);
                string jsBlock = "<script>SetPorgressBar('导出考生信息','" + ((1 * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>";
                Response.Write(jsBlock);
                Response.Flush();

                #region OWC11

                /*
                 * SpreadsheetClass xlsheet = new SpreadsheetClass();
                 * Worksheet ws = (Worksheet)xlsheet.Worksheets[1];
                 * ws.Cells.Font.set_Size(10);
                 * ws.Cells.Font.set_Name("宋体");
                 *
                 * ws.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单";
                 * Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]);
                 * range.set_MergeCells(true);
                 * range.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 * range.Font.set_Name("宋体");
                 *
                 *
                 * //write headertext
                 * ws.Cells[2, 1] = "序号";
                 * ((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 *
                 * ws.Cells[2, 2] = "姓名";
                 * ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true);
                 * ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 * if (PrjPub.IsWuhan())
                 * {
                 *  ws.Cells[2, 3] = "员工编码";
                 * }
                 * else
                 * {
                 *  ws.Cells[2, 3] = "工资编号";
                 * }
                 * ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true);
                 * ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 * ws.Cells[2, 4] = "职名";
                 * ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true);
                 * ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 * ws.Cells[2, 5] = "组织机构";
                 * ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true);
                 * ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 * ws.Cells[2, 8] = "考试地点";
                 * ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_MergeCells(true);
                 * ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 * int j = 0;
                 * foreach(DataRow dr in ds.Tables[0].Rows)
                 * {
                 *  ws.Cells[3 + j, 1] = j + 1;
                 *
                 *  ws.Cells[3 + j, 2] = dr["EmployeeName"].ToString();
                 *  ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true);
                 *  ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
                 *
                 *  ws.Cells[3 + j, 3] = "'" + dr["StrWorkNo"].ToString();
                 *  ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true);
                 *  ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
                 *
                 *
                 *  ws.Cells[3 + j, 4] = dr["PostName"].ToString();
                 *  ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true);
                 *  ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
                 *
                 *  ws.Cells[3 + j, 5] = dr["OrgName"].ToString();
                 *  ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true);
                 *  ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 *  ws.Cells[3 + j, 8] = dr["ComputeRoom"].ToString();
                 *  ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_MergeCells(true);
                 *  ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                 *
                 *  j++;
                 *
                 *  System.Threading.Thread.Sleep(10);
                 *  jsBlock = "<script>SetPorgressBar('导出考生信息','" + (((j + 1) * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>";
                 *  Response.Write(jsBlock);
                 *  Response.Flush();
                 * }
                 *
                 * ws.Name = "1-1";
                 * ws.Cells.Columns.AutoFit();
                 *
                 *
                 * ((Worksheet)xlsheet.Worksheets[1]).Activate();
                 *
                 * string path = Server.MapPath("../Excel/Excel.xls");
                 * if (File.Exists(path))
                 *  File.Delete(path);
                 * xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate);
                 */
                #endregion

                Excel.Application objApp   = new Excel.ApplicationClass();
                Excel.Workbooks   objbooks = objApp.Workbooks;
                Excel.Workbook    objbook  = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                Excel.Worksheet   objSheet = (Excel.Worksheet)objbook.Worksheets[1];              //取得sheet1
                Excel.Range       rang1;
                string            filename = "";

                try
                {
                    //生成.xls文件完整路径名
                    filename = Server.MapPath("/RailExamBao/Excel/Excel.xls");

                    if (File.Exists(filename.ToString()))
                    {
                        File.Delete(filename.ToString());
                    }
                    objSheet.Cells.Font.Size = 10;
                    objSheet.Cells.Font.Name = "宋体";

                    objSheet.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单";
                    rang1 = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 6]);
                    rang1.Merge(0);
                    rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    rang1.Font.Bold           = true;
                    objSheet.Cells.Font.Size  = 17;
                    objSheet.Cells.Font.Name  = "宋体";


                    objSheet.Cells.Font.Size = 12;
                    objSheet.Cells.Font.Name = "宋体";

                    //write headertext
                    objSheet.Cells[2, 1] = "序号";
                    ((Excel.Range)objSheet.Cells[2, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;


                    objSheet.Cells[2, 2] = "姓名";
                    objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[2, 3] = "员工编码";
                    objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[2, 4] = "职名";
                    objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[2, 5] = "组织机构(车间)";
                    objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[2, 6] = "考试地点";
                    objSheet.get_Range(objSheet.Cells[2, 6], objSheet.Cells[2, 6]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[2, 6], objSheet.Cells[2, 6]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    int j = 0;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        objSheet.Cells[3 + j, 1] = j + 1;

                        objSheet.Cells[3 + j, 2] = dr["EmployeeName"].ToString();
                        objSheet.get_Range(objSheet.Cells[6 + j, 2], objSheet.Cells[6 + j, 2]).Merge(0);
                        objSheet.get_Range(objSheet.Cells[6 + j, 2], objSheet.Cells[6 + j, 2]).HorizontalAlignment =
                            XlHAlign.xlHAlignLeft;

                        objSheet.Cells[3 + j, 3] = "'" + dr["StrWorkNo"].ToString();
                        objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).Merge(0);
                        objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).HorizontalAlignment =
                            XlHAlign.xlHAlignLeft;

                        objSheet.Cells[3 + j, 4] = dr["PostName"].ToString();
                        objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).Merge(0);
                        objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).HorizontalAlignment = XlHAlign.xlHAlignLeft;


                        objSheet.Cells[3 + j, 5] = dr["OrgName"].ToString();
                        objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).Merge(0);
                        objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).HorizontalAlignment = XlHAlign.xlHAlignLeft;

                        objSheet.Cells[3 + j, 6] = dr["ComputeRoom"].ToString();
                        objSheet.get_Range(objSheet.Cells[3 + j, 6], objSheet.Cells[3 + j, 6]).Merge(0);
                        objSheet.get_Range(objSheet.Cells[3 + j, 6], objSheet.Cells[3 + j, 6]).HorizontalAlignment =
                            XlHAlign.xlHAlignLeft;

                        j++;

                        System.Threading.Thread.Sleep(10);
                        jsBlock = "<script>SetPorgressBar('导出考生信息','" + (((j + 1) * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>";
                        Response.Write(jsBlock);
                        Response.Flush();
                    }

                    objSheet.Cells.Columns.AutoFit();

                    objApp.Visible = false;

                    objbook.Saved = true;
                    objbook.SaveCopyAs(filename);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objbook.Close(Type.Missing, filename, Type.Missing);
                    objbooks.Close();
                    objApp.Application.Workbooks.Close();
                    objApp.Application.Quit();
                    objApp.Quit();
                    GC.Collect();
                }
            }

            Response.Write("<script>top.returnValue='true';window.close();</script>");
        }
コード例 #14
0
        protected void btnOutPut_Click(object sender, EventArgs e)
        {
            try
            {
                RandomExamBLL             objBll        = new RandomExamBLL();
                RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("eid")));

                IList <Employee> objList = BindGrid();

                #region 原导出Excel方法
                //SpreadsheetClass xlsheet = new SpreadsheetClass();
                //Worksheet ws = (Worksheet)xlsheet.Worksheets[1];
                //ws.Cells.Font.set_Size(10);
                //ws.Cells.Font.set_Name("宋体");

                //ws.Cells[1, 1] = objRandomExam.ExamName + " 未参加考试学员名单";
                //Range rang1 = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]);
                //rang1.set_MergeCells(true);
                //rang1.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                //rang1.Font.set_Name("宋体");

                ////write headertext
                //ws.Cells[2, 1] = "序号";
                //((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

                //ws.Cells[2, 2] = "姓名";
                //ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true);
                //ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

                //if (PrjPub.IsWuhan())
                //{
                //    ws.Cells[2, 3] = "员工编码(身份证号码)";
                //}
                //else
                //{
                //    ws.Cells[2, 3] = "工资编号";
                //}

                //ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true);
                //ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

                //ws.Cells[2, 4] = "职名";
                //ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true);
                //ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

                //ws.Cells[2, 5] = "组织机构";
                //ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true);
                //ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

                //for (int j = 0; j < objList.Count; j++)
                //{
                //    ws.Cells[3 + j, 1] = j + 1;

                //    ws.Cells[3 + j, 2] = objList[j].EmployeeName;
                //    ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true);
                //    ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);

                //    ws.Cells[3 + j, 3] = "'" + objList[j].StrWorkNo;
                //    ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true);
                //    ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);


                //    ws.Cells[3 + j, 4] = objList[j].PostName;
                //    ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true);
                //    ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);

                //    ws.Cells[3 + j, 5] = objList[j].OrgName;
                //    ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true);
                //    ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                //}

                //ws.Name = "1-1";
                //ws.Cells.Columns.AutoFit();

                //((Worksheet)xlsheet.Worksheets[1]).Activate();

                //string path = Server.MapPath("../Excel/Excel.xls");
                //if (File.Exists(path))
                //    File.Delete(path);
                //xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate);
                #endregion

                Excel.Application objApp   = new Excel.ApplicationClass();
                Excel.Workbooks   objbooks = objApp.Workbooks;
                Excel.Workbook    objbook  = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                Excel.Worksheet   objSheet = (Excel.Worksheet)objbook.Worksheets[1];          //取得sheet1
                Excel.Range       rang1;
                string            filename = "";

                //生成.xls文件完整路径名
                filename = Server.MapPath("/RailExamBao/Excel/Excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

                if (File.Exists(filename.ToString()))
                {
                    File.Delete(filename.ToString());
                }
                objSheet.Cells.Font.Size = 10;
                objSheet.Cells.Font.Name = "宋体";

                objSheet.Cells[1, 1] = objRandomExam.ExamName + " 未参加考试学员名单";
                rang1 = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 7]);
                rang1.Merge(0);
                rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rang1.Font.Bold           = true;
                objSheet.Cells.Font.Size  = 17;
                objSheet.Cells.Font.Name  = "宋体";

                //write headertext
                objSheet.Cells[2, 1] = "序号";
                ((Excel.Range)objSheet.Cells[2, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                objSheet.Cells[2, 2] = "姓名";
                objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).Merge(0);
                objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                objSheet.Cells[2, 3] = "员工编码(身份证号码)";
                objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).Merge(0);
                objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                objSheet.Cells[2, 4] = "职名";
                objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).Merge(0);
                objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                objSheet.Cells[2, 5] = "组织机构";
                objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).Merge(0);
                objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                for (int j = 0; j < objList.Count; j++)
                {
                    objSheet.Cells[3 + j, 1] = j + 1;

                    objSheet.Cells[3 + j, 2] = objList[j].EmployeeName;
                    objSheet.get_Range(objSheet.Cells[3 + j, 2], objSheet.Cells[3 + j, 2]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[3 + j, 2], objSheet.Cells[3 + j, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[3 + j, 3] = "'" + objList[j].StrWorkNo;
                    objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[3 + j, 4] = objList[j].PostName;
                    objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    objSheet.Cells[3 + j, 5] = objList[j].OrgName;
                    objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).Merge(0);
                    objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                }

                objSheet.Columns.AutoFit();

                objApp.Visible = false;

                objbook.Saved = true;
                objbook.SaveCopyAs(filename);

                FileInfo file = new FileInfo(filename);
                this.Response.Clear();
                this.Response.Buffer          = true;
                this.Response.Charset         = "utf-7";
                this.Response.ContentEncoding = Encoding.UTF7;
                // 添加头信息,为"文件下载/另存为"对话框指定默认文件名
                this.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(objRandomExam.ExamName + "未参加考试学员名单") + ".xls");
                // 添加头信息,指定文件大小,让浏览器能够显示下载进度
                this.Response.AddHeader("Content-Length", file.Length.ToString());
                // 指定返回的是一个不能被客户端读取的流,必须被下载
                this.Response.ContentType = "application/ms-excel";
                // 把文件流发送到客户端
                this.Response.WriteFile(file.FullName);
            }
            catch
            {
                SessionSet.PageMessage = "系统错误,导出Excel文件失败!";
            }
        }
コード例 #15
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);
        }
コード例 #16
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);
        }