Esempio n. 1
0
        /// <summary>
        ///
        /// </summary>
        public void Export()
        {
            Excel.ApplicationClass e = Open(GetXlsFileName());
            if (e != null)
            {
                e.Cells[ptTitle.Y, ptTitle.X] = MakeTitle();
                int rowOffset = 0;
                foreach (DataRow row in _table.Rows)
                {
                    string team   = row[1].ToString().Trim();
                    string stName = row[2].ToString().Trim();
                    string person = row[3].ToString().Trim();
                    string dt     = row[5].ToString().Trim();

                    e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 0] = team;
                    e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 1] = stName;
                    e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 2] = person;
                    e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 3] = dt;
                    rowOffset++;
                }
                e.Visible = true;
//                e.Workbooks.Close();
                e = null;
                GC.Collect();
            }
        }
        private bool ValidateIsInstallExcel()
        {
            if (_HasInstalledExcel != null)
            {
                return(_HasInstalledExcel.Value);
            }

            try
            {
                Xls.Application excel;
                excel = new Xls.ApplicationClass();

                if (excel == null)
                {
                    AddError("请先安装Excel!");
                    _HasInstalledExcel = false;
                }
                else
                {
                    _HasInstalledExcel = true;
                    excel.Quit();
                    excel = null;
                    GC.Collect();
                }
            }
            catch
            {
                AddError("请先安装Excel!");
                _HasInstalledExcel = false;
            }
            return(_HasInstalledExcel.Value);
        }
Esempio n. 3
0
 void WriteH(Excel.ApplicationClass e, string[] h)
 {
     for (int i = 0; i < h.Length; i++)
     {
         e.Cells[2, i + 1] = h[i];
     }
 }
Esempio n. 4
0
        /// <summary>
        ///
        /// </summary>
        private void ExportXls()
        {
            Excel.ApplicationClass e = OpenExcel();
            if (e == null)
            {
                return;
            }

            string t = GetT();

            WriteT(e, t);

            string[] h = GetH();
            WriteH(e, h);

            int r = 3;

            for (int i = 0; i < lvGrStRds.Items.Count; i++)
            {
                int          c   = 1;
                ListViewItem lvi = lvGrStRds.Items[i];
                for (int j = 0; j < lvi.SubItems.Count; j++)
                {
                    e.Cells[r, c++] = lvi.SubItems[j].Text;
                }
                r++;
            }

            e.Visible       = true;
            e.DisplayAlerts = false;
        }
Esempio n. 5
0
        /// <summary>
        /// 将datatable的数据导出到excel
        /// </summary>
        /// <param name="mdt"></param>
        private void DataToExcel(DataTable mdt)
        {
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            Excel.Workbooks        oBooks = oExcel.Workbooks;

            Excel._Workbook oBook = null;

            oBook = (Excel._Workbook)(oExcel.Workbooks.Add(true));// 引用excel工作薄

            for (int i = 0; i < mdt.Columns.Count; i++)
            {
                oExcel.Cells[2, i + 1] = mdt.Columns[i].ColumnName.ToString();// m_DataView.Columns[i].HeaderText.ToString();
            }

            for (int i = 0; i < mdt.Rows.Count; i++)
            {
                for (int j = 0; j < mdt.Columns.Count; j++)
                {
                    oExcel.Cells[i + 3, j + 1] = mdt.Rows[i][j].ToString();
                }
            }

            oExcel.Visible = true;
            object Missing = System.Reflection.Missing.Value;

            //  oExcel.Run("Sheet1.printdoc", Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);

            oBook.Application.DisplayAlerts = false;
        }
Esempio n. 6
0
        // 运行excel的vba宏
        public object RunExcelMacro(string strMacroName, object args)
        {
            LuaTable ltArgs = args as LuaTable;

            if (ltArgs == null)
            {
                MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空!");
                return(null);
            }

            object[] oArgArr = new object[ltArgs.Values.Count];
            for (int i = 0; i < oArgArr.Length; i++)
            {
                oArgArr[i] = ltArgs[i];
            }

            object[] ArgArr30 = new object[30];
            for (int i = 0; i < oArgArr.Length; i++)
            {
                ArgArr30[i] = oArgArr[i];
            }
            for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
            {
                ArgArr30[i] = Missing.Value;
            }

            if (excelApp == null) // 未初始化成功
            {
                MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
                return(null); // 脚本已经对null值的处理
            }

            object result_macro = null;

            try
            {
                result_macro = excelApp.Run(strMacroName,
                                            ArgArr30[0], ArgArr30[1], ArgArr30[2], ArgArr30[3], ArgArr30[4], ArgArr30[5],
                                            ArgArr30[6], ArgArr30[7], ArgArr30[8], ArgArr30[9], ArgArr30[10], ArgArr30[11],
                                            ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
                                            ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
                                            ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
            }
            catch (Exception ex)
            {
                MessageBox.Show("宏运行错误,请不要保存所作修改!");
            }

            return(result_macro as string[]);

#if false
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook  = null;
            string          strXL  = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
            oBook = oBooks.Open(strXL, oMissing, oMissing,
                                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            RunMacro(oExcel, new Object[] { "矩形1_Click" });
Esempio n. 7
0
        /// <summary>
        ///
        /// </summary>
        public void Export()
        {
            // (row, col)
            //
            // StationName form (4,1)  row ++
            // Date        from (3,2)  col ++
            DateTime dt1 = DateTime.Now;

            Excel.ApplicationClass excel = OpenExcel();
            DateTime dt2 = DateTime.Now;

            MsgBox.Show("open excel: ", (dt2 - dt1).ToString());
            if (excel == null)
            {
                return;
            }

            excel.Cells[1, 1] = GetReportTitle();
            WriteDate(excel, this._beginDate, this._endDate);

            int rowOffset = 4;
            int colOffset = 2;

            for (int i = 0; i < _wccrSet.Count; i++)
            {
                WccResultsCollection wccs = _wccrSet[i];
                string stname             = wccs.StationName;
                int    row = rowOffset + i;
                excel.Cells[row, 1] = stname;

                for (int j = 0; j < wccs.Count; j++)
                {
                    WccResult wccr = wccs[j];
                    DateTime  dt   = wccr.Date;
                    int       wc   = wccr.WastingCaloric;
                    //int col = colOffset + j + 1;
                    int col = GetDateCol(dt) + colOffset;

                    // 0 - 无数据
                    // 1 - 只有一条记录无法计算
                    //
                    if (wccr.WastingCaloric > 0 &&
                        wccr.WastingCaloric != 0 &&
                        wccr.WastingCaloric != 1)
                    {
                        excel.Cells[row, col] = wccr.WastingCaloric;
                    }
                }
            }

            excel.Visible = true;
        }
Esempio n. 8
0
 /// <summary>
 /// Creates the Excel Application Class and sets the default web options
 /// </summary>
 private void CreateExcelAppClass()
 {
     if (m_excelApp == null)
     {
         //this ensures that there will only be one Excel process
         //running per thread since each thread runs this converter class
         m_excelApp = new Excel.ApplicationClass();
     }
     m_excelApp.Visible       = false;
     m_excelApp.DisplayAlerts = false;
     m_excelApp.Interactive   = false;
     m_excelApp.DefaultWebOptions.Encoding = Office.MsoEncoding.msoEncodingUTF8;
     m_excelApp.DefaultWebOptions.AlwaysSaveInDefaultEncoding = true;
 }
Esempio n. 9
0
        /// <summary>
        /// Quits the Excel Application.
        /// </summary>
        public void QuitExcel()
        {
            if (m_excelApp != null)
            {
                try
                {
                    //may need to re-set this value before quitting since something Excel may change it itself
                    m_excelApp.UserControl = false;
                    m_excelApp.Quit();
                }
                catch (Exception e)
                {
                    Logger.LogError("Failed to quit Excel", e);
                }

                m_excelApp = null;
            }
        }
Esempio n. 10
0
        public void Export()
        {
            Excel.ApplicationClass e = Open(GetFileName());
            if (e != null)
            {
                e.Cells[ptTitle.Y, ptTitle.X] = MakeTitle();
                int rowOffset = 0;
                foreach (DataRow row in _table.Rows)
                {
                    string Address     = row[0].ToString().Trim();
                    string StrTime     = row[1].ToString().Trim();
                    string BeforeLevel = row[2].ToString().Trim();
                    string BehindLevel = row[3].ToString().Trim();
                    string Hight       = row[4].ToString().Trim();
                    string Flus        = row[5].ToString().Trim();
                    string ReWater     = row[6].ToString().Trim();
                    string ToWater     = row[7].ToString().Trim();
                    // string YesWaUse    =row[8].ToString().Trim();
                    //string DayWaUse    =row[9].ToString().Trim();
                    //string Power     = row[8].ToString().Trim();
                    //string Lock     = row[9].ToString().Trim();

                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 0] = Address;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 1] = StrTime;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 2] = BeforeLevel;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 3] = BehindLevel;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 4] = Hight;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 5] = Flus;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 6] = ReWater;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 7] = ToWater;
                    // e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 8 ] = YesWaUse;
                    // e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 9 ] = DayWaUse;
                    //e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 8 ] = Power;
                    //e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 9 ] = Lock;
                    rowOffset++;
                }
                e.Visible = true;
                e         = null;
                GC.Collect();
            }
        }
Esempio n. 11
0
        public void Export()
        {
            Excel.ApplicationClass e = Open(GetFileName());
            if (e != null)
            {
                e.Cells[ptTitle.Y, ptTitle.X] = MakeTitle();
                int rowOffset = 0;
                foreach (DataRow row in _table.Rows)
                {
                    string Address    = row[0].ToString().Trim();
                    string StrTime    = row[1].ToString().Trim();
                    string Flus       = row[2].ToString().Trim();
                    string Efficiency = row[3].ToString().Trim();
                    string ReWater    = row[4].ToString().Trim();
                    // string YesReWater  =row[7].ToString().Trim();
                    // string DayWaterUse =row[8].ToString().Trim();
                    string ToWater = row[5].ToString().Trim();
                    string Run     = row[6].ToString().Trim();
                    // string ForceRun     = row[7].ToString().Trim();
                    //string Vibrate     = row[8].ToString().Trim();
                    // string Power     = row[9].ToString().Trim();

                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 0] = Address;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 1] = StrTime;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 2] = Flus;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 3] = Efficiency;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 4] = ReWater;
                    //  e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 5 ] = YesReWater;
                    //  e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 6 ] = DayWaterUse;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 5] = ToWater;
                    e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 6] = Run;
                    // e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 9 ] = Power;
                    rowOffset++;
                }
                e.Visible = true;

                e = null;
                GC.Collect();
            }
        }
Esempio n. 12
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        private void WriteDate(
            Excel.ApplicationClass excel,
            DateTime begin,
            DateTime end
            )
        {
            // date begin pos: row 3, col 2
            int row = 3;
            int col = 2;

            Debug.Assert(begin.Date < end.Date);

            DateTime dt        = begin.Date;
            int      colOffset = 0;

            while (dt <= end.Date)
            {
                string strDate = string.Format("{0}月{1}日", dt.Month, dt.Day);
                excel.Cells[row, col + colOffset] = strDate;
                colOffset++;
                dt = dt.AddDays(1);
            }
        }
        public void DataSet2Excel(DataSet ds, Dictionary <string, string> dicColumnNameMapping, string fileName)
        {
            if (ds == null || ds.Tables.Count == 0)
            {
                return;
            }

            if (ValidateIsInstallExcel() == false)
            {
                return;
            }

            #region 自动适应超出6万行就分Sheet
            int maxcount = 60000;
            for (int count = 0; count < ds.Tables.Count; count++)
            {
                if (ds.Tables[count].Rows.Count > maxcount)
                {
                    int zs = ds.Tables[count].Rows.Count;
                    int j  = zs / maxcount;
                    int j1 = zs % maxcount;
                    if (j1 > 0)
                    {
                        j = j + 1;
                    }
                    for (int a = 0; a < j; a++)
                    {
                        System.Data.DataTable dt = new System.Data.DataTable();
                        for (int i = 0; i < ds.Tables[count].Columns.Count; i++)
                        {
                            System.Data.DataColumn dc = new System.Data.DataColumn(ds.Tables[count].Columns[i].ColumnName);
                            dt.Columns.Add(dc);
                        }

                        int qs = a * maxcount;
                        int js = qs + maxcount;
                        if (js > zs)
                        {
                            js = zs;
                        }
                        for (int m = qs; m < js; m++)
                        {
                            System.Data.DataRow dr = dt.NewRow();
                            for (int n = 0; n < dt.Columns.Count; n++)
                            {
                                dr[n] = ds.Tables[count].Rows[m][n];
                            }
                            dt.Rows.Add(dr);
                        }
                        dt.TableName = ds.Tables[count].TableName + (a + 1).ToString();
                        ds.Tables.Add(dt);
                    }
                    ds.Tables.Remove(ds.Tables[count]);
                    count--;
                }
            }

            #endregion 自动适应超出6万行就分Sheet

            try
            {
                int totalCount   = 0;
                int currentCount = 0;
                for (int n = 0; n < ds.Tables.Count; n++)
                {
                    totalCount += ds.Tables[n].Rows.Count;
                }

                object omissing = System.Reflection.Missing.Value;
                Excel.ApplicationClass xlapp = new Excel.ApplicationClass();

                xlapp.DefaultFilePath     = "";
                xlapp.DisplayAlerts       = true;
                xlapp.SheetsInNewWorkbook = 1;

                Excel.Workbook xlworkbook = xlapp.Workbooks.Add(omissing);
                for (int i = ds.Tables.Count - 1; i >= 0; i--)
                {
                    System.Data.DataTable tmpDataTable = ds.Tables[i];
                    currentCount = DataTable2Excel(tmpDataTable, totalCount, currentCount, dicColumnNameMapping, omissing, xlworkbook);
                }

                if (!string.IsNullOrEmpty(fileName))
                {
                    xlworkbook.SaveCopyAs(fileName);
                    xlworkbook.Close(false, null, null);
                    xlapp.Quit();
                }
                else
                {
                    xlapp.Visible = true;
                }
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook);
                ReleaseExcelObject(xlworkbook, xlapp);
            }
            catch (Exception ex)
            {
                GC.Collect();
                AddError(ex.Message);
            }
        }
Esempio n. 14
0
        /// <summary>
        /// 将二维数组数据写入Excel文件(套用模板并分页)
        /// </summary>
        /// <param name="arr">二维数组</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2...”</param>
        public void ArrayToExcel(string[,] arr, int rows, int top, int left, string sheetPrefixName)
        {
            int      rowCount   = arr.GetLength(0);                   //二维数组行数(一维长度)
            int      colCount   = arr.GetLength(1);                   //二维数据列数(二维长度)
            int      sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
            DateTime beforeTime;
            DateTime afterTime;

            if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
            {
                sheetPrefixName = "Sheet";
            }

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime   = DateTime.Now;

            //打开模板文件,得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                                                         missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
            }

            #region 将二维数组数据写入Excel
            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;          //记录起始行索引
                int endRow   = i * rows;                //记录结束行索引

                //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                {
                    endRow = rowCount;
                }

                //获取要写入数据的WorkSheet对象,并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将二维数组中的数据写入WorkSheet
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        sheet.Cells[top + j, left + k] = arr[startRow + j, k];
                    }
                }

                Excel.TextBox txtAuthor  = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
                Excel.TextBox txtDate    = (Excel.TextBox)sheet.TextBoxes("txtDate");
                Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");

                txtAuthor.Text  = "lingyun_k";
                txtDate.Text    = DateTime.Now.ToShortDateString();
                txtVersion.Text = "1.0.0.0";
            }
            #endregion

            //输出Excel文件并退出
            try
            {
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook  = null;
                app       = null;

                GC.Collect();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Process[] myProcesses;
                DateTime  startTime;
                myProcesses = Process.GetProcessesByName("Excel");

                //得不到Excel进程ID,暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }
        }
Esempio n. 15
0
		public void ShowCategories(string db,string[] items, string path)
		{
		    int sheet = 1;
		    //Open the file to write
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range xlRange;
     
            object misValue = System.Reflection.Missing.Value;


			try
			{
	            xlApp = new Excel.ApplicationClass();
	            //xlApp = new Excel.ApplicationClass();
	            xlWorkBook = xlApp.Workbooks.Add(misValue);
	            
	            string workbookPath = path + "CategoryExport.xls";
	            try
	            {
	                xlWorkBook = xlApp.Workbooks.Open(workbookPath, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue);//,
	            	                                  //misValue,
	            	                                  //misValue);
	            }
	            catch (Exception e)
	            {
	                Console.WriteLine(e);
	                xlWorkBook.SaveAs(workbookPath,
	                                  Excel.XlFileFormat.xlWorkbookNormal,
	                                  misValue, 
	                                  misValue, 
	                                  misValue, 
	                                  misValue, 
	                                  Excel.XlSaveAsAccessMode.xlExclusive,
	                                  misValue, 
	                                  misValue, 
	                                  misValue, 
	                                  misValue);//,
	                                  //misValue);//
					xlWorkBook = xlApp.Workbooks.Open(workbookPath, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue);//,
	            	                                  //misValue,
	            	                                  //misValue);
	            }
	
	            //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
	            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
	            
	            //Open up connector the to database
	            SQLiteConnection connector = new SQLiteConnection(db);
	            connector.Open();
	            SQLiteCommand mycommand = new SQLiteCommand(connector);
	    
	            int j = 0;   
	            for (int i = 0; i < items.Length;i++)
	            {
	                float totalValue = 0.0f;
	                int cellNumber = 1;
	                while (String.Compare(items[i],".") == 0)
	                {
	                    i++;
	                }
	                string commandValue = "SELECT * FROM Transactions WHERE Category='";
	                commandValue += i.ToString();
	                commandValue += "'";
	                mycommand.CommandText = commandValue;
	                
	                SQLiteDataReader reader = mycommand.ExecuteReader();
	                xlWorkSheet.Cells[cellNumber++,j+1] = items[i];
	                while (reader.Read())
	                {
	                    xlWorkSheet.Cells[cellNumber++,j+1] = reader[3];
	                    totalValue += System.Convert.ToSingle(reader[3]);
	                }
	                xlWorkSheet.Cells[cellNumber++,j+1] = totalValue;
	                reader.Close();
	                j++;
	            }
	            // Set the range to fill.
	            xlRange = xlWorkSheet.get_Range("A1", "E100");
	            xlRange.Columns.AutoFit();
	            
	            xlWorkBook.Close(true, misValue, misValue);
	            xlApp.Quit();
	
	            releaseObject(xlWorkSheet);
	            releaseObject(xlWorkBook);
	            releaseObject(xlApp);	
			}
            catch (Exception COMException)
            {
            	Console.Write("COMException"+COMException);
            }
		}
Esempio n. 16
0
 void WriteT(Excel.ApplicationClass e, string t)
 {
     e.Cells[1, 1] = t;
 }
Esempio n. 17
0
        /// <summary>
        ///
        /// </summary>
        public void Export()
        {
            // (row, col)
            //
            // StationName form (4,1)  row ++
            // Date        from (3,2)  col ++
            DateTime dt1 = DateTime.Now;

            Excel.ApplicationClass excel = OpenExcel();
            DateTime dt2 = DateTime.Now;

            MsgBox.Show("open excel: ", (dt2 - dt1).ToString());
            if (excel == null)
            {
                return;
            }

            excel.Cells[1, 1] = GetReportTitle();
            WriteDate(excel, this._beginDate, this._endDate);

            int rowOffset = 4;
            int colOffset = 2;

//            for( int i=0; i<_wccrSet.Count; i++ )
//            {
//                WccResultsCollection wccs = _wccrSet[i];
//                string stname = wccs.StationName;
//                int row = rowOffset + i;
//                excel.Cells[ row, 1 ] = stname;
//
//                for( int j=0; j<wccs.Count; j++ )
//                {
//                    WccResult wccr = wccs[j];
//                    DateTime dt = wccr.Date;
//                    int wc = wccr.WastingCaloric;
//                    //int col = colOffset + j + 1;
//                    int col = GetDateCol( dt ) + colOffset;
//
//                    // 0 - 无数据
//                    // 1 - 只有一条记录无法计算
//                    //
//                    if ( wccr.WastingCaloric > 0 &&
//                        wccr.WastingCaloric != 0 &&
//                        wccr.WastingCaloric != 1 )
//                        excel.Cells[ row, col ] = wccr.WastingCaloric;
//                }
//            }

            int rowidx = 0;

            foreach (object obj in _wdpSet)
            {
                ArrayList array     = (ArrayList)obj;
                bool      writeName = false;

                foreach (object obj2 in array)
                {
                    WaterDataPoint wdp = (WaterDataPoint)obj2;
                    int            col = colOffset + GetDateCol(wdp.Dt);
                    int            row = rowOffset + rowidx;

                    if (!writeName)
                    {
                        excel.Cells[row, 1] = wdp.Name;
                        writeName           = true;
                    }
                    //if ( wdp.UsedWater != 0 )
                    excel.Cells[row, col] = wdp.UsedWater;
                }
                rowidx++;
            }

            excel.Visible = true;
        }
Esempio n. 18
0
        void InputInfoToDataBase(string file)
        {
            FileInfo fi = new FileInfo(file);
            if (!fi.Exists) return;
            string PhoneNum = fi.Directory.Name;
            Excel.Application excel;						//声明excel对象

            excel=new Excel.ApplicationClass();				//创建对象实例,这时在系统进程中会多出一个excel进程

                object missing=System.Reflection.Missing.Value;					//Missing 用于调用带默认参数的方法。
                object readOnly=true;
                excel.Visible=false;											//是否显示excel文档

                //Open Original Excel File
                excel.Application.Workbooks.Open(file,missing,readOnly,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);

                Excel.Workbook myBook=excel.Workbooks[1];					   //Workbooks从1开始计数的
                Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1]; //从1开始计数的
                int i = 1;
                int j = 1;
                string date;
                while (!((date=((Excel.Range)mySheet.Cells[i, 1]).Text.ToString())).Contains("-"))
                {
                    i++;
                }
                i++;
                string time=((Excel.Range)mySheet.Cells[i, 1]).Text.ToString();

                while (time.Contains("-") || time.Contains(":"))
                {
                    while(time.Contains(":"))
                    {
                        string date_time=date+" "+time;
                        //DateTime dt=DateTime.Parse(date_time);
                       // Console.WriteLine(file);
                       // label2.Text = PhoneNum;
                        //label4.Text = date_time;
                        string tl=((Excel.Range)mySheet.Cells[i, 4]).Text.ToString();
                        string ll = ((Excel.Range)mySheet.Cells[i, 5]).Text.ToString();
                       // label6.Text=tl;
                       // label8.Text=ll;
                        label2.Invoke(new SetTextDelegate(SetText), PhoneNum, date_time, tl, ll);

                        InsertItem(PhoneNum, date_time,tl ,ll );

                        i++;
                        time = ((Excel.Range)mySheet.Cells[i, 1]).Text.ToString();
                    }
                    if (time.Contains("-"))
                    {
                        date = time;
                    }
                    else
                    {
                        break;
                    }
                    i++;
                    time = ((Excel.Range)mySheet.Cells[i, 1]).Text.ToString();
                }
                         //设置该矩形框的文本格式

                //Save As  Original Excel File To CurrentPath

                //释放Excel对象,但在Asp.net Web程序中只有转向另一个页面的时候进程才结束
                //可以考虑使用KillExcelProcess()杀掉进程
                //ReleaseComObject 方法递减运行库可调用包装的引用计数。详细信息见MSDN
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                myBook.Close(null,null,null);
                excel.Workbooks.Close();
                mySheet=null;
                myBook=null;
                missing=null;
                readOnly=null;
                excel.Quit();
                excel=null;
        }
Esempio n. 19
0
        /// <summary>
        /// ��DataTable�����ݵ�����ʾΪ����
        /// </summary>
        /// <param name="dt">Ҫ����������</param>
        /// <param name="strTitle">��������ı���</param>
        /// <param name="FilePath">�����ļ���·��</param>
        /// <returns></returns>
        public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 4;
            int colIndex = 1;

            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;

            //ȡ���б���
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[4, colIndex] = col.ColumnName;

                //���ñ����ʽΪ���ж���
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//����Ϊdz��ɫ��������56��
            }

            //ȡ�ñ���е�����
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//���������͵��ֶθ�ʽΪ���ж���
                    }
                    else
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//�����ַ��͵��ֶθ�ʽΪ���ж���
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                }
            }

            //����һ���ϼ���
            int rowSum = rowIndex + 1;
            int colSum = 2;
            excel.Cells[rowSum, 2] = "�ϼ�";
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //����ѡ�еIJ��ֵ���ɫ
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//����Ϊdz��ɫ��������56��

            //ȡ����������ı���
            excel.Cells[2, 2] = strTitle;

            //������������ı����ʽ
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;

            //���ñ�����Ϊ����Ӧ���
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //������������ı���Ϊ���о���
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //���Ʊ߿�
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//��������߼Ӵ�
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//�����ϱ��߼Ӵ�
            xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//�����ұ��߼Ӵ�
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//�����±��߼Ӵ�

            afterTime = DateTime.Now;

            //��ʾЧ��
            //excel.Visible=true;
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  ����Excel����

            //��Ҫ��Excel��DCOM�����������:dcomcnfg

            //excel.Quit();
            //excel=null;

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();

            //ע�⣺�����õ�������Excel����Ҫִ����������������������Excel����
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//��������
            #endregion

            return filename;
        }
Esempio n. 20
0
        public void DataSet2Excel(DataSet ds, Dictionary<string, string> dicColumnNameMapping, string fileName)
        {
            if (ds == null || ds.Tables.Count == 0)
                return;

            if (ValidateIsInstallExcel() == false) return;

            #region 自动适应超出6万行就分Sheet
            int maxcount = 60000;
            for (int count = 0; count < ds.Tables.Count; count++)
            {

                if (ds.Tables[count].Rows.Count > maxcount)
                {
                    int zs = ds.Tables[count].Rows.Count;
                    int j = zs / maxcount;
                    int j1 = zs % maxcount;
                    if (j1 > 0)
                        j = j + 1;
                    for (int a = 0; a < j; a++)
                    {
                        System.Data.DataTable dt = new System.Data.DataTable();
                        for (int i = 0; i < ds.Tables[count].Columns.Count; i++)
                        {
                            System.Data.DataColumn dc = new System.Data.DataColumn(ds.Tables[count].Columns[i].ColumnName);
                            dt.Columns.Add(dc);
                        }

                        int qs = a * maxcount;
                        int js = qs + maxcount;
                        if (js > zs)
                            js = zs;
                        for (int m = qs; m < js; m++)
                        {
                            System.Data.DataRow dr = dt.NewRow();
                            for (int n = 0; n < dt.Columns.Count; n++)
                            {
                                dr[n] = ds.Tables[count].Rows[m][n];
                            }
                            dt.Rows.Add(dr);
                        }
                        dt.TableName = ds.Tables[count].TableName + (a + 1).ToString();
                        ds.Tables.Add(dt);
                    }
                    ds.Tables.Remove(ds.Tables[count]);
                    count--;

                }
            }

            #endregion 自动适应超出6万行就分Sheet

            try
            {
                int totalCount = 0;
                int currentCount = 0;
                for (int n = 0; n < ds.Tables.Count; n++)
                {
                    totalCount += ds.Tables[n].Rows.Count;
                }

                object omissing = System.Reflection.Missing.Value;
                Excel.ApplicationClass xlapp = new Excel.ApplicationClass();

                xlapp.DefaultFilePath = "";
                xlapp.DisplayAlerts = true;
                xlapp.SheetsInNewWorkbook = 1;

                Excel.Workbook xlworkbook = xlapp.Workbooks.Add(omissing);
                for (int i = ds.Tables.Count - 1; i >= 0; i--)
                {
                    System.Data.DataTable tmpDataTable = ds.Tables[i];
                    currentCount = DataTable2Excel(tmpDataTable, totalCount, currentCount, dicColumnNameMapping, omissing, xlworkbook);
                }

                if (!string.IsNullOrEmpty(fileName))
                {
                    xlworkbook.SaveCopyAs(fileName);
                    xlworkbook.Close(false, null, null);
                    xlapp.Quit();
                }
                else
                {
                    xlapp.Visible = true;
                }
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook);
                ReleaseExcelObject(xlworkbook, xlapp);
            }
            catch (Exception ex)
            {
                GC.Collect();
                AddError(ex.Message);
            }
        }
Esempio n. 21
0
        /// <summary>
        /// DataSet导出成Excel,调用函数或实例化后一定要写垃圾回收,否则会造成内存泄漏,GC.Collect();
        /// </summary>
        /// <param name="Excel_DS">要导出的DataSet</param>
        public bool ToExcel(DataSet Excel_DS, string strTitle)
        {
            try
            {
                // 判断是否有数据表
                if (Excel_DS.Tables.Count < 1)
                {
                    MessageBox.Show("没有数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return false;
                }
                else
                {
                    Excel.Application xlApp = new Excel.ApplicationClass();
                    if (xlApp == null)
                    {
                        MessageBox.Show("Excel无法启动,可能是没有安装或已损坏", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return false;
                    }
                    // 新建一个保存窗口,并设置默认属性
                    SaveFileDialog saveFileDialog = new SaveFileDialog();
                    saveFileDialog.DefaultExt = ".xls";                                     // 默认扩展名
                    saveFileDialog.AddExtension = true;                                     // 允许自动添加设置的默认扩展名
                    saveFileDialog.RestoreDirectory = true;                                 // 对话框关闭后恢复默认目录
                    saveFileDialog.Filter = "Microsoft Excel 工作薄 (*.xls)|*.xls";
                    saveFileDialog.Title = "导出到 Microsoft Excel 工作薄";
                    DialogResult result = saveFileDialog.ShowDialog();                      // 显示保存对话框

                    if (result == DialogResult.OK)
                    {
                        // 创建Excel工作薄
                        Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                        Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];

                        // 循环DataSet中的表
                        for (int i = 0; i < Excel_DS.Tables.Count; i++)
                        {
                            // 列索引,行索引,总列数,总行数
                            int ColIndex = 0;
                            int RowIndex = 0;
                            int ColCount = Excel_DS.Tables[i].Columns.Count;
                            int RowCount = Excel_DS.Tables[i].Rows.Count;

                            // 创建缓存数据
                            object[,] objData = new object[RowCount + 1, ColCount];

                            xlSheet.Name = Excel_DS.Tables[i].TableName;                    // 表单名

                            // 获取列标题
                            for (int k = 0; k < ColCount; k++)
                            {
                                objData[RowIndex, ColIndex++] = Excel_DS.Tables[i].Columns[k].Caption;
                            }

                            // 获取数据
                            for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
                            {
                                for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
                                {
                                    objData[RowIndex, ColIndex] = Excel_DS.Tables[i].Rows[RowIndex - 1][ColIndex];
                                }
                                System.Windows.Forms.Application.DoEvents();
                            }

                            // 写入Excel的样式,设置标题,列名等
                            Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]);
                            //xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]).MergeCells = true;           // 合并指定单元格
                            //xlApp.ActiveCell.FormulaR1C1 = strTitle;                                                    // 设置标题
                            //xlApp.ActiveCell.Font.Size = 15;                                                            // 标题字体大小
                            //xlApp.ActiveCell.Font.Bold = 1;                                                             // 标题字体加粗
                            //xlApp.ActiveCell.RowHeight = 50;                                                            // 标题栏行高
                            //xlApp.ActiveCell.Interior.ColorIndex = 42;                                                  // 标题栏背景色
                            //xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlLeft;                              // 标题栏对齐方式

                            //xlSheet.Cells.Font.Name = "Arial";                                                          // 全表单中字体
                            //xlSheet.Cells.Columns.WrapText = true;                                                      // 全表单的单元格自动换行

                            //xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, 1]).ColumnWidth = 20;                   // 第一列的列宽
                            //xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Interior.ColorIndex = 28;    // 列名栏背景色
                            //xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Font.Size = 10;              // 列名栏字体大小
                            //xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Font.Bold = 1;               // 列名栏字体加粗
                            //xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).HorizontalAlignment = Excel.Constants.xlCenter;      // 列名栏对齐方式,水平居中
                            //xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Borders.LineStyle = XlLineStyle.xlContinuous;        // 列名栏边框样式

                            //xlSheet.Cells[RowCount + 3, 1] = "End of the Report!";
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).MergeCells = true;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).Interior.ColorIndex = 28;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).Font.Size = 10;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).Font.Bold = 1;

                            //xlSheet.Cells[RowCount + 4, 1] = "* If the number of organization is less than 3, the city index information will be unavaliable.";
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 4, 1], xlApp.Cells[RowCount + 4, ColCount]).MergeCells = true;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 4, 1], xlApp.Cells[RowCount + 4, ColCount]).Font.Size = 10;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 4, 1], xlApp.Cells[RowCount + 4, ColCount]).Font.Bold = 1;

                            //xlSheet.Cells[RowCount + 5, 1] = "* TM: Top Management; M: Management; P: Professional; S: Staff; B: Blue Collar";
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 5, 1], xlApp.Cells[RowCount + 5, ColCount]).MergeCells = true;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 5, 1], xlApp.Cells[RowCount + 5, ColCount]).Font.Size = 10;
                            //xlSheet.get_Range(xlApp.Cells[RowCount + 5, 1], xlApp.Cells[RowCount + 5, ColCount]).Font.Bold = 1;

                            // 设置表单内数据的字体大小
                            for (int n = 0; n < RowCount; n++)
                            {
                                xlSheet.get_Range(xlApp.Cells[n + 2, 1], xlApp.Cells[n + 2, ColCount]).Font.Size = 9;
                            }

                            // 数据写入Excel
                            range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount + 1, ColCount]);
                            range.Value2 = objData;
                        }
                        object oMissiong = System.Reflection.Missing.Value;         //通过反射产生一个函数的默认参数
                        xlSheet.Application.DisplayAlerts = false;                  //禁止保存提示
                        xlSheet.SaveAs(saveFileDialog.FileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);         //Microsoft Excel 对象类库11.0以上版本时有10个参数,11以下有9个参数,如果少参数则用oMissiong填入
                        xlApp.Quit();
                    }
                    else
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Esempio n. 22
0
        private bool ExcelDocument(string strPath,string savePath)
        {
            try
            {
                Excel.Application excelApp = new Excel.ApplicationClass();
                excelApp.Visible = false;
                try
                {
                    Excel.Workbook newWorkbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                }
                catch(Exception ee)
                {
                    string errorText = "Unable to open specified WorkBook. Access is denied. "+ ee.Message ;
                    Console.Write(errorText +"\n");
                    return false;
                }
                try
                {
                    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(strPath,
                        0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);
                    try
                    {
                        excelWorkbook.SaveAs(savePath,Excel.XlFileFormat.xlHtml,"","",false,false,XlSaveAsAccessMode.xlNoChange,false,false,false,false,true);
                    }
                    catch(Exception ee)
                    {
                        string errorText = "Unable to open specified WorkBook. Access is denied. "+ ee.Message ;
                        Console.Write(errorText +"\n");
                        return false;
                    }
                    excelApp.Quit();

                    excelApp = null; //GC
                    GC.Collect(); //GC
                    return true;
                }
                catch(Exception ee)
                {
                    string errorText = "Unable to open specified WorkBook. Access is denied. "+ ee.Message ;
                    Console.Write(errorText +"\n");
                    return false;
                }
                return false;
            }
            catch(Exception ee)
            {
            }
            return false;
        }
Esempio n. 23
0
        /// <summary>
        ///方法,导出DataGridView中的数据到Excel文件
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name= "dgv"> DataGridView </param>
        public static void DataGridViewToExcel(MultiColHeaderDgv dgv)
        {
            #region

            //申明保存对话框
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀
            dlg.DefaultExt = "xls ";
            //文件后缀列表
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框
            if (dlg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            //返回文件路径
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效
            if (fileNameString.Trim() == " ")
            {
                return;
            }
            //定义表格内数据的行数和列数
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            //行数必须大于0
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数必须大于0
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //行数不可以大于65536
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数不可以大于255
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //验证以fileNameString命名的文件是否存在,如果存在删除它
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            #endregion
            Excel.Application objExcel    = new Excel.Application();
            Excel.Workbook    objWorkbook = null;
            Excel.Worksheet   objsheet    = null;

            try
            {
                //申明对象
                objExcel = new Excel.ApplicationClass();

                //objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                //objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
                objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;


                //设置EXCEL不可见
                objExcel.Visible = false;

                //向Excel中写入表格的表头
                int displayColumnsCount = 1;

                //Excel.Range
                Excel.Range range1  = objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[3, 1]);
                Excel.Range range2  = objExcel.get_Range(objExcel.Cells[1, 2], objExcel.Cells[3, 2]);
                Excel.Range range3  = objExcel.get_Range(objExcel.Cells[1, 3], objExcel.Cells[3, 3]);
                Excel.Range range4  = objExcel.get_Range(objExcel.Cells[1, 4], objExcel.Cells[3, 4]);
                Excel.Range range5  = objExcel.get_Range(objExcel.Cells[1, 5], objExcel.Cells[3, 5]);
                Excel.Range range6  = objExcel.get_Range(objExcel.Cells[1, 6], objExcel.Cells[3, 6]);
                Excel.Range range7  = objExcel.get_Range(objExcel.Cells[1, 7], objExcel.Cells[3, 7]);
                Excel.Range range8  = objExcel.get_Range(objExcel.Cells[2, 8], objExcel.Cells[3, 8]);
                Excel.Range range9  = objExcel.get_Range(objExcel.Cells[1, 8], objExcel.Cells[1, 11]);
                Excel.Range range10 = objExcel.get_Range(objExcel.Cells[2, 9], objExcel.Cells[2, 11]);

                range1.Merge(0);
                range2.Merge(0);
                range3.Merge(0);
                range4.Merge(0);
                range5.Merge(0);
                range6.Merge(0);
                range7.Merge(0);
                range8.Merge(0);
                range9.Merge(0);
                range9.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;     // 文本水平居中方式
                range10.Merge(0);
                range10.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range1.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range2.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range3.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range4.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range5.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range6.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range7.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range8.NumberFormatLocal    = "@";  //设置单元格格式为文本
                range9.NumberFormatLocal    = "@";  //设置单元格格式为文本
                objsheet.Cells[1, 1]        = "财务分类";
                objsheet.Cells[1, 2]        = "项目代码";
                objsheet.Cells[1, 3]        = "项目名称";
                objsheet.Cells[1, 4]        = "项目内涵";
                objsheet.Cells[1, 5]        = "除外内容";
                objsheet.Cells[1, 6]        = "计价单位";
                objsheet.Cells[1, 7]        = "说明";
                objsheet.Cells[2, 8]        = "省定价";
                objsheet.Cells[3, 9]        = "三档";
                objsheet.Cells[3, 10]       = "二档";
                objsheet.Cells[3, 11]       = "一档";
                objsheet.Cells[1, 8]        = "价格(元)";
                objsheet.Cells[2, 9]        = "市定价格";

                range2.ColumnWidth = 12;                                    //设置单元格的宽度
                range3.ColumnWidth = 20;                                    //设置单元格的宽度
                range4.ColumnWidth = 35;                                    //设置单元格的宽度
                range5.ColumnWidth = 25;                                    //设置单元格的宽度
                range7.ColumnWidth = 25;                                    //设置单元格的宽度

                range2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
                range3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
                range4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
                range5.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
                range7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式

                Excel.Range rangGol = objsheet.get_Range("A1", "K" + dgv.RowCount.ToString());
                rangGol.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;     // 文本水平居中方式
                rangGol.WrapText            = true;

                //向Excel中逐行逐列写入表格中的数据
                for (int row = 0; row <= dgv.RowCount - 1; row++)
                {
                    //tempProgressBar.PerformStep();

                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (dgv.Columns[col].Visible == true)
                        {
                            try
                            {
                                objExcel.Cells[row + 4, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                                displayColumnsCount++;
                            }
                            catch (Exception)
                            {
                            }
                        }
                    }
                }

                //保存文件
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用
                if (objWorkbook != null)
                {
                    objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                }
                if (objExcel.Workbooks != null)
                {
                    objExcel.Workbooks.Close();
                }
                if (objExcel != null)
                {
                    objExcel.Quit();
                }

                objsheet    = null;
                objWorkbook = null;
                objExcel    = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
Esempio n. 24
0
        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>");
        }
Esempio n. 25
0
        public void Export(System.Data.DataView source, string fileName, string sheetName, ArrayList columnsProperties)

        {
            this._columnsProperties = columnsProperties;
            System.IO.StreamWriter excelDoc;
            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n<Workbook " +
                                         "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
                                         " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                                         "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
                                         "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
                                         "office:spreadsheet\">\r\n <Styles>\r\n " +
                                         "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                                         "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
                                         "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
                                         "\r\n <Protection/>\r\n </Style>\r\n " +
                                         "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
                                         "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
                                         "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
                                         " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
                                         "ss:ID=\"Decimal\">\r\n <NumberFormat " +
                                         "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
                                         "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
                                         "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
                                         "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
                                         "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
                                         "</Styles>\r\n ";
            const string endExcelXML = "</Workbook>";

            int rowCount   = 0;
            int sheetCount = 1;

            /*
             *                                                                                                 <xml version>
             *                                                                                                 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
             *                                                                                                 xmlns:o="urn:schemas-microsoft-com:office:office"
             *                                                                                                 xmlns:x="urn:schemas-microsoft-com:office:excel"
             *                                                                                                 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
             *                                                                                                 <Styles>
             *                                                                                                 <Style ss:ID="Default" ss:Name="Normal">
             *                                                                                                       <Alignment ss:Vertical="Bottom"/>
             *                                                                                                       <Borders/>
             *                                                                                                       <Font/>
             *                                                                                                       <Interior/>
             *                                                                                                       <NumberFormat/>
             *                                                                                                       <Protection/>
             *                                                                                                 </Style>
             *                                                                                                 <Style ss:ID="BoldColumn">
             *                                                                                                       <Font x:Family="Swiss" ss:Bold="1"/>
             *                                                                                                 </Style>
             *                                                                                                 <Style ss:ID="StringLiteral">
             *                                                                                                       <NumberFormat ss:Format="@"/>
             *                                                                                                 </Style>
             *                                                                                                 <Style ss:ID="Decimal">
             *                                                                                                       <NumberFormat ss:Format="0.0000"/>
             *                                                                                                 </Style>
             *                                                                                                 <Style ss:ID="Integer">
             *                                                                                                       <NumberFormat ss:Format="0"/>
             *                                                                                                 </Style>
             *                                                                                                 <Style ss:ID="DateLiteral">
             *                                                                                                       <NumberFormat ss:Format="mm/dd/yyyy;@"/>
             *                                                                                                 </Style>
             *                                                                                                 </Styles>
             *                                                                                                 <Worksheet ss:Name="Sheet1">
             *                                                                                                 </Worksheet>
             *                                                                                                 </Workbook>
             */
            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            ArrayList aux = new ArrayList();

            foreach (PropertiesColumn pc in this._columnsProperties)
            {
                if (pc.Visible)
                {
                    aux.Add(pc);
                    excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                    excelDoc.Write(pc.ColumnCaption);
                    excelDoc.Write("</Data></Cell>");
                }
            }
            excelDoc.Write("</Row>");
            foreach (DataRowView x in source)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>");                 //ID=" + rowCount + "
                foreach (PropertiesColumn pc in aux)
                {
                    if (source.Table.Columns.Contains(pc.ColumnName))
                    {
                        System.Type rowType = x.Row[pc.ColumnName].GetType();
                        switch (rowType.ToString())
                        {
                        case "System.String":
                            string XMLstring = x.Row[pc.ColumnName].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "");
                            XMLstring = XMLstring.Replace(">", ")");
                            XMLstring = XMLstring.Replace("<", "(");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;

                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate
                            //to the format above
                            DateTime XMLDate         = (DateTime)x.Row[pc.ColumnName];
                            string   XMLDatetoString = "";                                   //Excel Converted Date
                            XMLDatetoString = XMLDate.Year.ToString() +
                                              "-" + (XMLDate.Month < 10 ? "0" + XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                              "-" + (XMLDate.Day < 10 ? "0" + XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                              "T" + (XMLDate.Hour < 10 ? "0" + XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                              ":" + (XMLDate.Minute < 10 ? "0" + XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) + ":" +
                                              (XMLDate.Second < 10 ? "0" + XMLDate.Second.ToString() : XMLDate.Second.ToString()) + ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" + "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;

                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(x.Row[pc.ColumnName].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;

                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                           "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x.Row[pc.ColumnName].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;

                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                           "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x.Row[pc.ColumnName].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;

                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;

                        default:
                            throw(new Exception(rowType.ToString() + " not handled."));
                        }
                    }
                    else
                    {
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                       "<Data ss:Type=\"String\">");
                        excelDoc.Write("");
                        excelDoc.Write("</Data></Cell>");
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();

            Excel.ApplicationClass EXL = new Excel.ApplicationClass();
            workbook = EXL.Workbooks.Open(fileName,
                                          0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                                          true, false, 0, true, false, false);
            EXL.Visible = true;
            Worksheet worksheet = (Worksheet)EXL.ActiveSheet;

            worksheet.Activate();
        }
Esempio n. 26
0
        /// <summary>
        /// 将DataTable的数据导出显示为报表
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 4;
            int colIndex = 1;

            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;

            //取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[4, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色,共计有56种
            }


            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    }
                    else
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                }
            }

            //加载一个合计行			
            int rowSum = rowIndex + 1;
            int colSum = 2;
            excel.Cells[rowSum, 2] = "合计";
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //设置选中的部分的颜色			
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色,共计有56种

            //取得整个报表的标题			
            excel.Cells[2, 2] = strTitle;

            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;

            //设置报表表格为最适应宽度			
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
            xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗



            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }
Esempio n. 27
0
        /// <summary>
        /// 将DataTable数据写入Excel文件(套用模板并分页)
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2...”</param>
        public void DataTableToExcel(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName)
        {
            int      rowCount   = dt.Rows.Count;                      //源DataTable行数
            int      colCount   = dt.Columns.Count;                   //源DataTable列数
            int      sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
            DateTime beforeTime;
            DateTime afterTime;

            if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
            {
                sheetPrefixName = "Sheet";
            }

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime   = DateTime.Now;

            //打开模板文件,得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                                                         missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
            }

            #region 将源DataTable数据写入Excel
            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;          //记录起始行索引
                int endRow   = i * rows;                //记录结束行索引

                //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                {
                    endRow = rowCount;
                }

                //获取要写入数据的WorkSheet对象,并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将dt中的数据写入WorkSheet
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
                    }
                }

                //写文本框数据
                //Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
                //Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
                //Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");

                //txtAuthor.Text = "lingyun_k";
                //txtDate.Text = DateTime.Now.ToShortDateString();
                //txtVersion.Text = "1.0.0.0";
            }
            #endregion

            /*
             * try
             * {
             * // MessageProcess.ZAMessageShowWarning(this.outputFile);
             *  workBook.SaveAs(this.outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
             *  workBook.Close(null, null, null);
             *  app.Workbooks.Close();
             *  app.Application.Quit();
             *  app.Quit();
             *
             * // System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
             * //  System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
             * //  System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
             *
             *  workSheet = null;
             *  workBook = null;
             *  app = null;
             *
             *  GC.Collect();
             * }
             * catch (Exception e)
             * {
             *  throw e;
             * }
             * finally
             * {
             *  Process[] myProcesses;
             *  DateTime startTime;
             *  myProcesses = Process.GetProcessesByName("Excel");
             *
             *  //得不到Excel进程ID,暂时只能判断进程启动时间
             *  foreach (Process myProcess in myProcesses)
             *  {
             *      startTime = myProcess.StartTime;
             *
             *      if (startTime > beforeTime && startTime < afterTime)
             *      {
             *          myProcess.Kill();
             *      }
             *  }
             * }*/
        }
Esempio n. 28
0
        // 运行excel的vba宏
        public object RunExcelMacro(string strMacroName, object args)
        {
            LuaTable ltArgs = args as LuaTable;
            if (ltArgs == null)
            {
                MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空!");
                return null;
            }

            object[] oArgArr = new object[ltArgs.Values.Count];
            for (int i = 0; i < oArgArr.Length; i++)
            {
                oArgArr[i] = ltArgs[i];
            }

            object[] ArgArr30 = new object[30];
            for (int i = 0; i < oArgArr.Length; i++)
            {
                ArgArr30[i] = oArgArr[i];
            }
            for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
            {
                ArgArr30[i] = Missing.Value;
            }

            if(excelApp == null) // 未初始化成功
            {
                MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
                return null; // 脚本已经对null值的处理 
            }

            object result_macro = null;
            try
            {
                result_macro = excelApp.Run(strMacroName,
                ArgArr30[0],  ArgArr30[1],  ArgArr30[2],  ArgArr30[3],  ArgArr30[4],  ArgArr30[5],
                ArgArr30[6],  ArgArr30[7],  ArgArr30[8],  ArgArr30[9],  ArgArr30[10], ArgArr30[11],
                ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
                ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
                ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
            }
            catch (Exception ex)
            {
                MessageBox.Show("宏运行错误,请不要保存所作修改!");
            }

            return (result_macro as string[]);
#if false
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook = null;
            string strXL = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
            oBook = oBooks.Open(strXL, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            RunMacro(oExcel, new Object[] { "矩形1_Click" });
Esempio n. 29
0
        private bool ValidateIsInstallExcel()
        {
            if (_HasInstalledExcel != null) return _HasInstalledExcel.Value;

            try
            {
                Xls.Application excel;
                excel = new Xls.ApplicationClass();

                if (excel == null)
                {
                    AddError("请先安装Excel!");
                    _HasInstalledExcel = false;
                }
                else
                {
                    _HasInstalledExcel = true;
                    excel.Quit();
                    excel = null;
                    GC.Collect();
                }
            }
            catch
            {
                AddError("请先安装Excel!");
                _HasInstalledExcel = false;
            }
            return _HasInstalledExcel.Value;
        }
Esempio n. 30
0
        /// <summary>
        /// 将DataTable的数据导出显示为报表
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// /// <param name="fileName">保存文件的文件名</param>
        /// <returns></returns>
        public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath, string fileName)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook   xBk;
            Excel._Worksheet  xSt;

            int rowIndex = 4;
            int colIndex = 1;

            excel = new Excel.ApplicationClass();
            xBk   = excel.Workbooks.Add(true);
            xSt   = (Excel._Worksheet)xBk.ActiveSheet;

            //取得列标题
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[4, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold           = true;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色,共计有56种
            }


            //取得表格中的数据
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    }
                    else
                    if (col.DataType == System.Type.GetType("System.String"))
                    {
                        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;    //设置字符型的字段格式为居中对齐
                    }
                    else
                    {
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
            }

            //加载一个合计行
            int rowSum = rowIndex + 1;
            int colSum = 2;

            excel.Cells[rowSum, 2] = "合计";
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //设置选中的部分的颜色
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色,共计有56种

            //取得整个报表的标题
            excel.Cells[2, 2] = strTitle;

            //设置整个报表的标题格式
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //设置整个报表的标题为跨列居中
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight  = Excel.XlBorderWeight.xlThick;               //设置左边线加粗
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;               //设置上边线加粗
            xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick; //设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight  = Excel.XlBorderWeight.xlThick; //设置下边线加粗



            afterTime = DateTime.Now;

            //显示效果
            //excel.Visible=true;
            //excel.Sheets[0] = "sss";

            //ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyy-MM-dd HHmmssff " + fileName) + ".xls";

            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return(filename);
        }
Esempio n. 31
0
		public void ExcelExport(int sheet, int cells, Budget budgetClass, string path,int year)
		{
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range xlRange;
            //Excel.Application xlApp;
            //Excel.Workbook xlWorkBook;
            //Excel.Worksheet xlWorkSheet;
     
            object misValue = System.Reflection.Missing.Value;


            try
            {
	            xlApp = new Excel.ApplicationClass();
	            //xlApp = new Excel.ApplicationClass();
	            xlWorkBook = xlApp.Workbooks.Add(misValue);
	            
	            string workbookPath = path + "budgeting" + year.ToString() + ".xls";
	            //xlWorkBook = xlApp.Workbooks.Open(workbookPath, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);//
	            try
	            {
					xlWorkBook = xlApp.Workbooks.Open(workbookPath, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue);//,
	            	                                  //misValue,
	            	                                  //misValue);
	            }
	            catch (Exception e)
	            {
	                Console.WriteLine(e);
	                xlWorkBook.SaveAs(workbookPath,
	                                  Excel.XlFileFormat.xlWorkbookNormal,
	                                  misValue, 
	                                  misValue, 
	                                  misValue, 
	                                  misValue, 
	                                  Excel.XlSaveAsAccessMode.xlExclusive,
	                                  misValue, 
	                                  misValue, 
	                                  misValue, 
	                                  misValue);//,
	                                  //misValue);//
					xlWorkBook = xlApp.Workbooks.Open(workbookPath, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue,
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue, 
	            	                                  misValue);//,
	            	                                  //misValue,
	            	                                  //misValue);
	            }
	
	            //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
	            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
	            //the column must be + 3 due to the month starting at 0 and the columns being 1
	            //and then having a label and a budgeted amount column
	            //the rows start at 1 also which means you have to add 2 to the row for
	            //the label
	            //Re-add in all the data just in case it has never been put in.
	            xlWorkSheet.Cells[1,1] = "Budget Cat";
	            xlWorkSheet.Cells[1,2] = "Budget Amt";
	            xlWorkSheet.Cells[1,3] = "January";
	            xlWorkSheet.Cells[1,4] = "February";
	            xlWorkSheet.Cells[1,5] = "March";
	            xlWorkSheet.Cells[1,6] = "April";
	            xlWorkSheet.Cells[1,7] = "May";
	            xlWorkSheet.Cells[1,8] = "June";
	            xlWorkSheet.Cells[1,9] = "July";
	            xlWorkSheet.Cells[1,10] = "August";
	            xlWorkSheet.Cells[1,11] = "September";
	            xlWorkSheet.Cells[1,12] = "October";
	            xlWorkSheet.Cells[1,13] = "November";
	            xlWorkSheet.Cells[1,14] = "December";
	            
	            SQLiteConnection connector = new SQLiteConnection("Data Source=2013Tomo.d");
	            connector.Open();
	            SQLiteCommand mycommand = new SQLiteCommand(connector);
	            SQLiteDataReader reader;
	                    
	            for (int i=0;i<budgetClass.totalBudget;i++)
	            {
	                xlWorkSheet.Cells[i+2, 1] = budgetClass.budgetCategories[i];
	            }            
	            for (int i=0;i<budgetClass.totalBudget;i++)
	            {
	                xlWorkSheet.Cells[i+2, 2] = budgetClass.budgetedAmount[i];
	            }    
	            for (int month=0;month<12;month++)
	            {
	                for (int i=0;i<budgetClass.totalBudget;i++)
	                {
	                    string command = "select Total(Amount) from Transactions where Category=";
	                    command += i.ToString();
	                    command += " and month="+month.ToString();
	                    mycommand.CommandText = command;
	                    reader = mycommand.ExecuteReader();
	                    reader.Read();
	                    float amountValue = System.Convert.ToSingle(reader[0]);
	                    mycommand.Dispose();
	                    command = "select Total(Amount) from AmazonTransactions where Category=";
	                    command += i.ToString();
	                    command += " and month="+month.ToString();
	                    mycommand.CommandText = command;
	                    reader = mycommand.ExecuteReader();
	                    reader.Read();
	                    float amountValue2 = System.Convert.ToSingle(reader[0]);
	                    mycommand.Dispose();
	                                        
	                    xlWorkSheet.Cells[i+2, month+3] = amountValue-amountValue2;//budgetClass.budgetSpentAmount[i];
	                }
	            }
	            for (int i=0;i<budgetClass.totalBudget;i++)
	            {
	                    string command = "select Total(Amount) from Transactions where Category=";
	                    command += i.ToString();
	                    mycommand.CommandText = command;
	                    reader = mycommand.ExecuteReader();
	                    reader.Read();
	                    float amountValue = System.Convert.ToSingle(reader[0]);
	                    mycommand.Dispose();
	                    command = "select Total(Amount) from AmazonTransactions where Category=";
	                    command += i.ToString();
	                    mycommand.CommandText = command;
	                    reader = mycommand.ExecuteReader();
	                    reader.Read();
	                    float amountValue2 = System.Convert.ToSingle(reader[0]);
	                    mycommand.Dispose();
	                                        
	                    xlWorkSheet.Cells[i+2, 15] = amountValue-amountValue2;//budgetClass.budgetSpentAmount[i];                
	            }
	            connector.Close();
	            
	            // Set the range to fill.
	            xlRange = xlWorkSheet.get_Range("A1", "E100");
	            xlRange.Columns.AutoFit();
	            
	            xlWorkBook.Close(true, misValue, misValue);
	            xlApp.Quit();
	
	            releaseObject(xlWorkSheet);
	            releaseObject(xlWorkBook);
	            releaseObject(xlApp);	        	        
            }
            catch (Exception COMException)
            {
            	Console.Write("COMException"+COMException);
            }
	    }