Beispiel #1
0
 /// <summary>
 /// 功能:实现Excel应用程序的打开
 /// </summary>
 /// <param name="TemplateFilePath">模板文件物理路径</param>
 public void Open(string TemplateFilePath)
 {
     //打开对象
     m_objExcel               = new Microsoft.Office.Interop.Excel.Application();
     m_objExcel.Visible       = false;
     m_objExcel.DisplayAlerts = false;
     if (m_objExcel.Version != "11.0")
     {
         MessageBox.Show("您的Excel 版本不是11.0 (Office 2003),操作可能会出现问题。");
         m_objExcel.Quit();
         return;
     }
     m_objBooks = (Workbooks)m_objExcel.Workbooks;
     if (TemplateFilePath.Equals(String.Empty))
     {
         m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt));
     }
     else
     {
         m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
     }
     m_objSheets = (Sheets)m_objBook.Worksheets;
     m_objSheet  = (_Worksheet)(m_objSheets.get_Item(1));
     m_objExcel.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
 }
Beispiel #2
0
 public void newWorkbook(string excelTemplate, string fileName)
 {
     //以excelTemplate为模板新建文件fileName
     //excelApplication.
     xlsWb        = xlsWbs.Add(excelTemplate);
     SaveFileName = "";
     SaveAsExcel(false);
 }
Beispiel #3
0
 public Base()
 {
     App               = new Application();
     App.Visible       = true;
     App.DisplayAlerts = false;
     Books             = App.Workbooks;
     Book              = Books.Add(Missing.Value);
     Sheet             = Book.ActiveSheet;
 }
Beispiel #4
0
 public void Open(string FileName)//打开一个Excel文件
 {
     app = new Excel.Application();
     wbs = app.Workbooks;
     wb  = wbs.Add(FileName);
     //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
     //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
     mFilename = FileName;
 }
Beispiel #5
0
    private void ExportExcel(DataTable dt)
    {
        if (dt == null)
        {
            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.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] = transColumnName(dt.Columns[i].ColumnName);
            range = (Excel.Range)worksheet.Cells[1, i + 1];
            range.Interior.ColorIndex = 15;
            range.Font.Bold           = true;
        }

        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];
            }
            rowRead++;
            percent = ((float)(100 * rowRead)) / totalCount;
        }
        xlApp.Visible = true;
    }
Beispiel #6
0
 /// <summary>
 /// 新建一个Excel实例
 /// </summary>
 /// <param name="strTitle">Excel表头上的文字</param>
 public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
 {
     m_objExcel         = new Excel.ApplicationClass();
     m_objExcel.Visible = true;
     m_objBooks         = (Excel.Workbooks)m_objExcel.Workbooks;
     m_objBook          = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
     m_objSheets        = (Excel.Sheets)m_objBook.Worksheets;
     if (intSheetTotalSize <= 3)
     {
         if (this.dbTotalSize <= this.dbSheetSize)
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
         else if (this.dbTotalSize <= this.dbSheetSize * 2)
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
         else
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
     }
     for (int i = 3; i < intSheetTotalSize; i++)
     {
         m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
     }
     ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
     for (int i = 2; i <= m_objSheets.Count; i++)
     {
         ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
     }
 }
        static void Main(string[] args)
        {
            Console.WriteLine("Interop Performance Test - 10.000 Cells.");

            /*
             * start excel and disable messageboxes and screen updating
             */
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts  = false;
            excelApplication.ScreenUpdating = false;
            //excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);

            /*
             *  create new empty worksheet
             */
            Excel.Workbooks books  = excelApplication.Workbooks;
            Excel.Workbook  book   = books.Add(Missing.Value);
            Excel.Sheets    sheets = book.Worksheets;
            Excel.Worksheet sheet  = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            /*
             *  do the test
             *  we collect all references and release after time recording
             *  the 2 latebind libs release the references at end automaticly and we want a fair test
             */
            List <object> comReferenesList = new List <object>();
            DateTime      timeStart        = DateTime.Now;

            for (int i = 1; i <= 10000; i++)
            {
                string rangeAdress = "$A" + i.ToString();
                Range  cellRange   = (Range)sheet.Range[rangeAdress];
                cellRange.Value        = "value";
                cellRange.Font.Name    = "Verdana";
                cellRange.NumberFormat = "@";
                cellRange.WrapText     = false;
                Comment sampleComment = cellRange.AddComment("Sample Comment");
                comReferenesList.Add(cellRange);
                comReferenesList.Add(sampleComment);
            }
            DateTime timeEnd     = DateTime.Now;
            TimeSpan timeElapsed = timeEnd - timeStart;

            foreach (var item in comReferenesList)
            {
                Marshal.ReleaseComObject(item);
            }

            /*
             * display for user
             */
            string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);

            Console.WriteLine(outputConsole);
            Console.Read();

            /*
             * write result in logfile
             */
            string logFile       = Path.Combine(Environment.CurrentDirectory, "Interop.log");
            string logFileAppend = timeElapsed.ToString() + Environment.NewLine;

            File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);

            /*
             * release & quit
             */
            Marshal.ReleaseComObject(sheet);
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(book);
            Marshal.ReleaseComObject(books);

            excelApplication.Quit();
            Marshal.ReleaseComObject(excelApplication);
        }
Beispiel #8
0
        /// <summary>
        /// 将datatable导出到excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="saveasfilepath"></param>
        /// <param name="WorkbookName"></param>
        public void ExportExcel(System.Data.DataTable dt, string saveasfilepath, string WorkbookName)
        {
            if (dt == null)
            {
                return;
            }
            string saveFileName = saveasfilepath;  // "d:\\333.xlsx";
            bool   fileSaved    = false;

            Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                throw new Exception("无法创建Excel对象,可能您的机子未安装Excel");
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook  workbook  = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            if (!string.IsNullOrEmpty(WorkbookName))
            {
                Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
                ws.Name = WorkbookName;
            }
            Excel.Range range;
            //string oldCaption = DateTime.Today.ToString("yy-MM-dd");
            long  totalCount = dt.Rows.Count;
            long  rowRead    = 0;
            float percent    = 0;

            // worksheet.Cells[1, 1] = DateTime.Today.ToString("yy-MM-dd");
            //写入字段
            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;
            }
            //写入数值

            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];
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                //this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度.
                System.Windows.Forms.Application.DoEvents();
            }
            //this.lbl_process.Visible = false;

            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle  = Excel.XlLineStyle.xlContinuous;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight     = Excel.XlBorderWeight.xlThin;

            if (dt.Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle  = Excel.XlLineStyle.xlContinuous;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight     = Excel.XlBorderWeight.xlThin;
            }

            if (saveFileName != "")
            {
                try
                {
                    //xlApp.Visible = true;
                    //workbook.PrintPreview(false);
                    workbook.Close(true, saveFileName, Type.Missing);
                    workbook = null;
                    //SaveAs方法会截断超过255个字符的字符串
                    //workbook.Saved = true;
                    //workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel7, Type.Missing,
                    //    Type.Missing, Type.Missing, Type.Missing,
                    //    Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                    //    Type.Missing, Type.Missing, Type.Missing);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    xlApp.Quit();
                    GC.Collect();
                    fileSaved = false;
                    throw new Exception("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            xlApp = null;
            GC.Collect();//强行销毁
            if (fileSaved && File.Exists(saveFileName))
            {
                //System.Diagnostics.Process.Start(saveFileName);
            }
        }
Beispiel #9
0
        /// <summary>
        /// 把DataTable中的数据导出到Excel
        /// </summary>
        /// <param name="dt"></param>
        public static void DataTableToExcel(System.Data.DataTable srcDt, string savename)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt = srcDt;

            if (dt == null)
            {
                return;
            }

            string saveFileName = "";
            bool   fileSaved    = false;

            /*SaveFileDialog saveDialog = new SaveFileDialog();
             * saveDialog.DefaultExt = "xlsx";
             * saveDialog.Filter = "Excel文件|*.xlsx";
             * saveDialog.FileName = "导出文件";
             * saveDialog.ShowDialog();
             * saveFileName = saveDialog.FileName;
             * if (saveFileName.IndexOf(":") < 0) 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
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth     = 40;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight      = 50;

            //worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText          = true;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            for (int i = 2; i < 12; i++)
            {
                if (dt.Rows[i - 2][3].ToString() == "E")
                {
                    worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
                }
                if (dt.Rows[i - 2][3].ToString() == "W")
                {
                    worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
                }
            }
            //写入字段

            worksheet.Cells[1, 1] = "参数名称";
            worksheet.Cells[1, 2] = "参数值";
            worksheet.Cells[1, 3] = "推荐值";
            worksheet.Cells[1, 4] = "报警值";
            worksheet.Cells[1, 5] = "详情";
            worksheet.Cells[1, 6] = "参数类型";
            worksheet.Cells[1, 7] = "重要级别";
            worksheet.Cells[1, 8] = "历史变化";


            //写入数值

            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];
                }
                System.Windows.Forms.Application.DoEvents();
            }
            //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

            /*string md5 = "";
             * for (int r = 0; r < 5; r++)
             * {
             *  for (int i = 0; i < 8; i++)
             *  {
             *      md5 += dt.Rows[r][i].ToString();
             *      worksheet.Cells[2, 9] = md5;
             *  }
             *
             * }
             */

            //worksheet.Cells.Width = 39;
            //if (saveFileName != "")
            //{
            try
            {
                workbook.Saved = true;
                saveFileName   = savename;
                workbook.SaveCopyAs(saveFileName);
                fileSaved = true;
                MessageBox.Show("导出完成!");
            }
            catch (Exception ex)
            {
                fileSaved = false;
                MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
            }
            //}
            //else
            //{
            // fileSaved = false;
            //}

            xlApp.Quit();
            GC.Collect();//强行销毁
            if (fileSaved && System.IO.File.Exists(saveFileName))
            {
                System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
            }
        }
Beispiel #10
0
    public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
    {
        //文件存在时先删除文件后再进行下一步操作
        FileInfo file = new FileInfo(strFullFilePath);

        if (file.Exists)
        {
            file.Delete();
        }
        int rowIndex = 3; //开始写入数据的单元格行
        int colIndex = 0; //开始写入数据的单元格列

        System.Reflection.Missing miss   = System.Reflection.Missing.Value;
        Excel.ApplicationClass    mExcel = new Excel.ApplicationClass();
        mExcel.Visible = false;
        Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
        Excel.Workbook  mBook  = (Excel.Workbook)(mBooks.Add(miss));
        Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
        Excel.Range     er     = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
        er.Value2 = title;
        try
        {
            foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
            {
                colIndex++;
                mSheet.Cells[3, colIndex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows) //同样方法处理数据
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    if (colIndex == 2)
                    {
                        mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
                    }
                    else
                    {
                        mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
            }
            //保存工作已写入数据的工作表
            mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
            // return true;
        }
        catch (Exception ee)
        {
            throw new Exception(ee.Message);
        }
        finally //finally中的代码主要用来释放内存和中止进程()
        {
            mBook.Close(false, miss, miss);
            mBooks.Close();
            mExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
            GC.Collect();
        }
        //return false;
    }
        public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
        {
            _excelApplication = new Excel.Application();
            _excelApplication.Visible = visible;

            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            _workBook = (Excel._Workbook)(_workBooks.Add(filePath));

            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
            _excelSheet.Name = sheetName;
        }
        public void OpenExcelApp(bool visible)
        {
            _excelApplication = new Excel.Application();
            _excelApplication.Visible = visible;

            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            _workBook = (Excel._Workbook)(_workBooks.Add(_value));

            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
        }
        private void ExportUpEmployee()
        {
            string       strSql = @"select  GetOrgName(GetStationOrgID(b.org_Id))   UnitName,
                            getworkshopname(b.org_id) WorkShopName,
                            case when c.level_num=4  then c.Short_Name else null end WorkGroupName,
                            Employee_Name, Identity_CardNo,
                            e.Train_Plan_Name,
                            d.Class_Name,f.Post_Name
                             from zj_train_plan_employee t 
                            inner join Employee b on t.Employee_ID=b.Employee_ID
                            inner join Org c on b.Org_ID=c.Org_ID
                            inner join ZJ_Train_Plan_Post_Class d on t.Train_Plan_Post_Class_ID=d.Train_Plan_Post_Class_ID
                            inner join ZJ_Train_Plan e on d.Train_Plan_ID=e.Train_Plan_ID
                            inner join Post f on b.Post_ID = f.Post_ID
                            where t.Train_Plan_Post_Class_Org_ID=" + Request.QueryString.Get("classOrgID");
            OracleAccess db     = new OracleAccess();
            DataSet      ds     = db.RunSqlDataSet(strSql);

            if (ds.Tables[0].Rows.Count == 0)
            {
                Response.Write("<script>alert('没有已上报的员工信息!');window.close();</script>");
                return;
            }

            string       templateFileName = Server.MapPath("/RailExamBao/RandomExam/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 jsBlock;

            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
            string            filename    = "";
            string            strName     = "";
            string            strFileName = "";

            try
            {
                int count = ds.Tables[0].Rows.Count;
                strName     = "Excel";
                strFileName = ds.Tables[0].Rows[0]["UnitName"].ToString() + ds.Tables[0].Rows[0]["Class_Name"].ToString() + "上报人员";
                filename    = Server.MapPath("/RailExamBao/Excel/" + strName + ".xls");

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

                int colIndex = 1;
                objSheet.Cells[1, colIndex] = "序号";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "站段";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "车间";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "班组";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "姓名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "身份证号";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "职名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "上报培训计划名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "上报计划培训班名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                System.Threading.Thread.Sleep(10);
                jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(1 * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
                Response.Write(jsBlock);
                Response.Flush();

                int rowIndex = 2;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    colIndex = 1;
                    objSheet.Cells[rowIndex, colIndex] = rowIndex - 1;
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["UnitName"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["WorkShopName"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["WorkGroupName"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Employee_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = "'" + dr["Identity_CardNo"];
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Post_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Train_Plan_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Class_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    System.Threading.Thread.Sleep(10);
                    jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(rowIndex * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
                    Response.Write(jsBlock);
                    Response.Flush();

                    rowIndex++;
                }

                objSheet.Cells.Columns.AutoFit();

                objApp.Visible = false;

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

                // 处理完成
                jsBlock = "<script>SetCompleted('处理完成。'); </script>";
                Response.Write(jsBlock);
                Response.Flush();
            }
            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='" + strName + "|" + strFileName + "';window.close();</script>");
        }
Beispiel #14
0
 public void Create()//创建一个Excel对象
 {
     app = new Excel.Application();
     wbs = app.Workbooks;
     wb  = wbs.Add(true);
 }