コード例 #1
0
        //导出Excel
        private void button1_Click(object sender, EventArgs e)
        {
            ClsExcel cExcel = null;

            cExcel = new ClsExcel();

            string fileName  = "人员列表_" + DateTime.Now.ToString("yyyyMMddHHmmss"); //Excel文件名
            string sheetName = "全体人员";                                            //sheet页的名称

            cExcel.ExportToExcel(ref dataGridView1, fileName, sheetName);
            cExcel.Close(false);
        }
コード例 #2
0
        /// <summary>
        /// 打印Excel文档
        /// </summary>
        /// <param name="excel"></param>
        public void PrintOut(ClsExcel excel)
        {
            int iCounter = excel.WorkSheetCount();

            for (int i = 1; i <= iCounter; i++)
            {
                excel.SetActiveSheet(i);
                excel.PrintOut();
            }

            excel.Close(false);
        }
コード例 #3
0
        /// <summary>
        /// 打印Excel文档
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="isLandcape">是否横向打印 true 为是</param>
        public void PrintOut(ClsExcel excel, bool isLandcape)
        {
            int iCounter = excel.WorkSheetCount();

            for (int i = 1; i <= iCounter; i++)
            {
                excel.SetActiveSheet(i);

                if (true == isLandcape)
                {
                    xSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                }

                excel.PrintOut();
            }

            excel.Close(false);
        }
コード例 #4
0
        /// <summary>
        /// 格式化空白行
        /// </summary>
        /// <param name="cExcel"></param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="rowHeight">行高</param>
        /// <param name="border">边框样式</param>
        public void FormatBlank(ClsExcel cExcel, int rowIndex, double rowHeight, int border)
        {
            Excel.Worksheet xSheet = cExcel.X_Sheet;
            Excel.Range     xRange;

            try
            {
                xRange = (Excel.Range)xSheet.get_Range(xSheet.Cells[rowIndex, 1],
                                                       xSheet.Cells[rowIndex, 1]);
                xRange.RowHeight         = rowHeight;
                xRange.Borders.LineStyle = border;
            }
            catch (Exception ex)
            {
                throw (new Exception("[FormatBlank]:" + ex.Message));
            }
            finally
            {
                xRange = null;
            }
        }
コード例 #5
0
        /// <summary>
        /// 格式化列标题
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="dt"></param>
        //public void FormatHeadText(int rowIndex, int colIndex)
        //{
        //    Excel.Range xRange;

        //    try
        //    {
        //        xRange = (Excel.Range)xSheet.get_Range(xSheet.Cells[rowIndex, 1],
        //                         xSheet.Cells[rowIndex, colIndex]);
        //        xSheet.Columns.AutoFit();
        //        xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 居中对齐
        //        xRange.Font.Bold = true;
        //        xRange.WrapText = true;

        //    }
        //    catch (Exception e)
        //    {
        //        throw (new Exception("SetTextFormat:" + e.Message));
        //    }
        //    finally
        //    {
        //        xRange = null;
        //    }
        //}

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="cExcel"></param>
        /// <param name="beginRowIndex">起始行索引</param>
        /// <param name="beginColIndex">起始列索引</param>
        /// <param name="endRowIndex">结束行索引</param>
        /// <param name="endColIndex">结束列索引</param>
        public void FormatMergeCell(ClsExcel cExcel, int beginRowIndex, int beginColIndex,
                                    int endRowIndex, int endColIndex)
        {
            Excel.Worksheet xSheet = cExcel.X_Sheet;
            Excel.Range     xRange;

            try
            {
                xRange = (Excel.Range)xSheet.get_Range(xSheet.Cells[beginRowIndex, beginColIndex],
                                                       xSheet.Cells[endRowIndex, endColIndex]);
                xRange.MergeCells = true;
            }
            catch (Exception ex)
            {
                throw new Exception("[FormatMergeCell]:" + ex.Message);
            }
            finally
            {
                xRange = null;
            }
        }
コード例 #6
0
        /// <summary>
        /// 将 DataGridView 中的数据导出到Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="rowBeginNum"></param>
        /// <param name="colBeginNum"></param>
        /// <param name="fileName"></param>
        public static void ExportDataGridView2Excel(ref DataGridView dgv, ref DataGridView dgv2, int rowBeginNum,
                                                    int colBeginNum, string fileName, string frmBarName, string sheet1Name, string sheet2Name)
        {
            if (null == fileName || 0 == fileName.Length)
            {
                return;
            }

            string openPeriodOne = sheet1Name + "以后未返校学生列表";
            string openPeriodTwo = sheet2Name + "以后未返校学生列表";

            int rowNum      = dgv.Rows.Count;
            int columnNum   = dgv.Columns.Count;
            int rowIndex    = 1 + rowBeginNum;
            int columnIndex = colBeginNum;

            int rowNum2      = dgv2.Rows.Count;
            int columnNum2   = dgv2.Columns.Count;
            int rowIndex2    = 1 + rowBeginNum;
            int columnIndex2 = colBeginNum;

            ClsExcel cExcel = null;

            //FrmProgressBar fmPB = new FrmProgressBar();

            try
            {
                //fmPB = ClsPubFuctions.GetProgressBarForm(frmBarName, (rowNum + columnNum + rowNum2 + columnNum2));
                //fmPB.Show();

                //Excel.Application.DoEvents();
                //ClsSystem.mainForm.Cursor = Cursors.WaitCursor;
                cExcel = new ClsExcel();

                // 复制Sheet
                cExcel.CopySheet(1, true);
                cExcel.SetActiveSheet(1);


                #region --openPeriodOne--

                for (int j = 0; j < columnNum; j++)
                {
                    if (!dgv.Columns[j].Visible || dgv.Columns[j].Name == "Check")
                    {
                        continue;
                    }

                    columnIndex++;
                    cExcel.SetValue(rowIndex, columnIndex, dgv.Columns[j].HeaderText);

                    if (0 != dgv.Rows.Count && null != dgv.Rows[0].Cells[j].Value)
                    {
                        cExcel.FormatHeadText(1, columnNum);
                    }
                }

                // 行循环
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;

                    for (int j = 0; j < columnNum; j++) // 列循环
                    {
                        if (!dgv.Columns[j].Visible || dgv.Columns[j].Name == "Check")
                        {
                            continue;
                        }

                        columnIndex++;

                        if (null != dgv.Rows[i].Cells[j].Value)
                        {
                            cExcel.SetValue(rowIndex, columnIndex, dgv.Rows[i].Cells[j].Value.ToString().Replace("\r\n", ""));
                        }
                    }

                    //fmPB.ProgressBar.Value += 1;
                }

                cExcel.X_Sheet.Name = openPeriodOne;
                #endregion

                // -------------------------------------以上为openPeriodOne-----------------------------

                #region --openPeriodTwo--
                cExcel.SetActiveSheet(2);

                for (int j = 0; j < columnNum2; j++)
                {
                    if (!dgv2.Columns[j].Visible || dgv2.Columns[j].Name == "Check")
                    {
                        continue;
                    }

                    columnIndex2++;
                    cExcel.SetValue(rowIndex2, columnIndex2, dgv2.Columns[j].HeaderText);

                    if (0 != dgv2.Rows.Count && null != dgv2.Rows[0].Cells[j].Value)
                    {
                        cExcel.FormatHeadText(1, columnNum2);
                    }

                    //fmPB.ProgressBar.Value += 1;
                }

                // 行循环
                for (int i = 0; i < rowNum2; i++)
                {
                    rowIndex2++;
                    columnIndex2 = 0;

                    for (int j = 0; j < columnNum2; j++) // 列循环
                    {
                        if (!dgv2.Columns[j].Visible || dgv2.Columns[j].Name == "Check")
                        {
                            continue;
                        }

                        columnIndex2++;

                        if (null != dgv2.Rows[i].Cells[j].Value)
                        {
                            cExcel.SetValue(rowIndex2, columnIndex2, dgv2.Rows[i].Cells[j].Value.ToString().Replace("\r\n", ""));
                        }
                    }

                    //fmPB.ProgressBar.Value += 1;
                }

                cExcel.X_Sheet.Name = openPeriodTwo;

                #endregion

                cExcel.xWorkBook.SaveCopyAs(fileName);
                //ClsSystem.mainForm.Cursor = Cursors.Default;
            }
            catch (Exception ex)
            {
                MessageBox.Show("ExportDataGridView2Excel:" + ex.ToString());
            }
            finally
            {
                //if (fmPB != null)
                //{
                //    fmPB.Close();
                //}

                //ClsSystem.mainForm.Cursor = Cursors.Default;

                if (cExcel != null)
                {
                    cExcel.Close(false);
                }
            }
        }