数据库向Excel写入数据

        /// <summary>
        /// 打开目标数据录入信息Excel文件
        /// </summary>
        /// <param name="pErrInfo">函数处理失败的错误信息</param>
        /// <returns>如果函数处理成功返回true，否则返回false</returns>
        private bool OpenDesSjlrExcel(string excelFile, ErrInfo pErrInfo)
        {
            bool pFlagOK = true;  //处理标志，表示是否处理正确
            short pErrPos = 0;
            bool pOccurredException = false;//是否发生了异常错误

            try
            {
                string templateFile = AppDomain.CurrentDomain.BaseDirectory + "转化生成的信息录入数据.xlsx";

                if (File.Exists(templateFile) == false)
                {
                    pFlagOK = false;
                    pErrInfo.Content.Append("模板文件丢失: " + templateFile);
                    MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
                else
                {
                    File.Copy(templateFile, excelFile);
                    File.SetAttributes(excelFile, FileAttributes.Normal);
                }

                if (pFlagOK)
                {
                    if (xlApp1 == null)
                    {
                        pFlagOK = false;
                        pErrInfo.Content.Append("Excel对象非法为空");
                        MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                    else
                    {
                        xlApp1.DefaultFilePath = "";
                        xlApp1.DisplayAlerts = true;
                        xlApp1.SheetsInNewWorkbook = 1;
                        xlBook1 = xlApp1.Workbooks.Open(excelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                              , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                              , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                        ws1 = (Microsoft.Office.Interop.Excel.Worksheet)xlBook1.Worksheets[1];

                        ws1.Name = "一级";

                        //创建列标题
                        ws1.Cells[1, 1] = "档案编号";
                        ws1.Cells[1, 2] = "姓名";
                        ws1.Cells[1, 3] = "单位名称";
                        ws1.Cells[1, 4] = "单位简称";
                        ws1.Cells[1, 5] = "旧身份证号";
                        ws1.Cells[1, 6] = "身份证号";

                        ws2 = (Microsoft.Office.Interop.Excel.Worksheet)xlBook1.Worksheets[2];

                        ws2.Name = "二级";

                        //创建列标题
                        ws2.Cells[1, 1] = "档案编号";
                        ws2.Cells[1, 2] = "编号";
                        ws2.Cells[1, 3] = "材料名称";
                        ws2.Cells[1, 4] = "材料制成时间";
                        ws2.Cells[1, 5] = "页数";
                        ws2.Cells[1, 6] = "备注";

                    }
                }
            }
            catch (Exception ex)
            {
                pFlagOK = false;
                pErrInfo.OccurredException = ex;
                pErrInfo.Content.Append(ex.Message);
                pOccurredException = true;//发生了异常错误
            }
            finally
            {
                //如果处理失败，并且有错误信息那么就把错误信息拼接上该函数的名字标识，便于主程序查找分析异常错误的位置
                if (!pFlagOK && pErrInfo.Content.Length > 0)
                {
                    //获取当前函数名称
                    string className = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName;
                    string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                    StringBuilder tempBuilder = new StringBuilder();
                    //用当前命名空间名.类名.函数名-->·错误信息的格式返回错误描述
                    tempBuilder.Append(className + "." + methodName + "-->");
                    //如果属于异常错误，则需要在错误信息中加入错误位置标记信息
                    if (pOccurredException)
                    {
                        tempBuilder.Append(" ErrPos: ");
                        tempBuilder.Append(pErrPos);
                        tempBuilder.Append(", ");
                    }
                    pErrInfo.Content.Insert(0, tempBuilder.ToString());
                    tempBuilder = null;

                }
            }

          //返回函数处理结果给外部的函数调用者
            return pFlagOK;
        }

 

 

     private void mtbFJ_Click(object sender, EventArgs e)
        {
            bool pFlagOK = true;  //处理标志，表示是否处理正确
            short pErrPos = 0;
            bool pOccurredException = false;//是否发生了异常错误
            ErrInfo pErrInfo = new ErrInfo();
            pArchvs = new Archvs();
            pArchvFils = new ArchvFiles();
            SqlDataReader dataReaderArchv = null;
            SqlDataReader dataReaderArchvFile = null;

            Archv pArchv = new Archv();

            try
            {
                if (comboBox1.Text == "阜阳数据导出模板")
                {
                    if (pFlagOK)
                    {
                        //打开目标数据录入Excel文件，便于后面写入处理
                        xlApp1 = new Microsoft.Office.Interop.Excel.Application();
                        //xlWord = new Microsoft.Office.Interop.Word.Application();
                        string excelFile = AppDomain.CurrentDomain.BaseDirectory + "\" + "转化生成的Excel录入数据(" + DateTime.Now.ToString("yyyy-MM-dd_HH_mm_ss_fff") + ").xlsx";
                        //string wordFile = AppDomain.CurrentDomain.BaseDirectory + "\" + "转化生成的Word录入数据(" + DateTime.Now.ToString("yyyy-MM-dd_HH_mm_ss_fff") + ").docx";

                        if (xlApp1 == null)
                        {
                            pFlagOK = false;
                            pErrInfo.Content.Append("无法创建Excel对象");
                            MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        }
                        else
                        {
                            if (OpenDesSjlrExcel(excelFile, pErrInfo) == false)
                            {
                                pFlagOK = false;
                                pErrInfo.Content.Insert(0, "打开目标数据录入Excel文件失败: ");
                                MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            }
                        }

 

                        if (pFlagOK)
                        {
                            c = listViewArchvs.SelectedIndices;
                            pArchvIDs = new string[c.Count];
                            for (int i = 0; i < c.Count; i++)
                            {
                                //pArchvIDs[i] = listViewArchvs.Items[i].SubItems[5].Text;
                                pArchvIDs[i] = listViewArchvs.Items[c[i]].SubItems[5].Text;
                            }

                            if (WriteArchvs(ref dataReaderArchv, pErrInfo) == false)
                            {
                                pFlagOK = false;
                                pErrInfo.Content.Insert(0, "向Excel中写入案卷信息失败 ");
                                MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            }

                            string pArchvTable = ((DALX)comboBoxDALX.SelectedItem).ArchvTable;

                            if (WriteArchvFiles(ref dataReaderArchvFile, pArchvTable, pErrInfo) == false)
                            {
                                pFlagOK = false;
                                pErrInfo.Content.Insert(0, "向Excel中写入卷内信息失败 ");
                                MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            }

                        }

 

            

                        if (pFlagOK)
                        {
                            xlBook1.Save();
                            xlBook1.Close();

                            xlBook1 = null;
                            xlApp1.Quit();
                            xlApp1 = null;

                            System.Diagnostics.Process.Start(excelFile);
                        }
                    }
                }

 

         catch (Exception ex)
            {
                pFlagOK = false;
                pErrInfo.OccurredException = ex;
                pErrInfo.Content.Append(ex.Message);
                pOccurredException = true;//发生了异常错误
            }
            finally
            {
                //如果处理失败，并且有错误信息那么就把错误信息拼接上该函数的名字标识，便于主程序查找分析异常错误的位置
                if (!pFlagOK && pErrInfo.Content.Length > 0)
                {
                    //获取当前函数名称
                    string className = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName;
                    string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                    StringBuilder tempBuilder = new StringBuilder();
                    //用当前命名空间名.类名.函数名-->·错误信息的格式返回错误描述
                    tempBuilder.Append(className + "." + methodName + "-->");
                    //如果属于异常错误，则需要在错误信息中加入错误位置标记信息
                    if (pOccurredException)
                    {
                        tempBuilder.Append(" ErrPos: ");
                        tempBuilder.Append(pErrPos);
                        tempBuilder.Append(", ");
                    }
                    pErrInfo.Content.Insert(0, tempBuilder.ToString());
                    tempBuilder = null;

                    //metroSideMessage1.ShowMessage(enumMessageIconType.InfoOrange, "提示", pErrInfo.toShortString());

                    //记录错误日志
                    AppLogger.WriteAppLog(pErrInfo.ToString());
                }

                //检测按钮复位为可用
                //mtbSCBB.Enabled = true;

                //txtBox.Enabled = true;
                //复位检错按钮
                //mtbCheckError.ButtonTextProTitleText = "0%";
                mtbSCBB.ButtonTextProContentText = "已完成";
            }

        }

 

 

        /// <summary>
        /// 向目标Excel表中写入案卷信息
        /// </summary>
        /// <param name="pErrInfo">函数处理失败的错误信息</param>
        /// <returns>如果函数处理成功返回true，否则返回false</returns>
        private bool WriteArchvs(ref SqlDataReader dataReader, ErrInfo pErrInfo)
        {
            bool pFlagOK = true;  //处理标志，表示是否处理正确
            short pErrPos = 0;
            bool pOccurredException = false;//是否发生了异常错误

            string pXM = "";
            string pDWMC = "";
            string pDH = "";
            string pSFZH = "";
            string pDWJC = "";
            string pJSFZH = "";

            ArchvService pArchvService = new ArchvService(DaoFactory.NewArchvDao(Common.g_DataSource, (DALX)comboBoxDALX.SelectedItem));
            int pArchvID = 0;

            if (pArchvIDs == null || pArchvIDs.Length == 0)
            {
                pFlagOK = false;
                pErrInfo.Content.Append("请选择需要生成报表的案卷");
                MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

 

            try
            {

                if (pFlagOK)
                {
                    pErrPos = 2;
                    for (int i = 0; i < pArchvIDs.Length; i++)
                    {
                        pArchvID = int.Parse(pArchvIDs[i]);
                        if (pArchvService.GetArchv(pArchvID, ref dataReader, pErrInfo) == false)
                        {
                            pFlagOK = false;
                            pErrInfo.Content.Append("未能查找到案卷ID：" + "'" + pArchvID + "'" + "的案卷信息，获取案卷信息失败。 ");
                            MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        }

                        DataAccessLib.DataReader dDataReader = new DataAccessLib.DataReader(dataReader);
                        while (dDataReader.Read())
                        {

                            pDH = dDataReader.GetString(Common.g_DALXs[0].DhFieldName);//档号
                            pXM = dDataReader.GetString(Common.g_DALXs[0].XmFieldName);//姓名
                          
                            pDWMC = dDataReader.GetString(Common.g_DALXs[0].DwmcFieldName);//单位名称
                            pDWJC = dDataReader.GetString(Common.g_DALXs[0].DwmcFieldName);//单位简称
                            pJSFZH = dDataReader.GetString(Common.g_DALXs[0].JsfzhmFieldName);//旧身份证号
                            pSFZH = dDataReader.GetString(Common.g_DALXs[0].SfzhmFieldName);//身份证号

                            //写入数据录入Excel文件
                            //姓名
                            ws1.Cells[m_CurrentArchvExcelRowIndex, 1] = pDH;
                            //单位名称
                            ws1.Cells[m_CurrentArchvExcelRowIndex, 2] = pXM;

                            //档号
                            ws1.Cells[m_CurrentArchvExcelRowIndex, 3] = pDWMC;
                            //身份证号
                            ws1.Cells[m_CurrentArchvExcelRowIndex, 4] = pDWJC;
                            //籍贯
                            ws1.Cells[m_CurrentArchvExcelRowIndex, 5] = pJSFZH;
                            //单位类型
                            ws1.Cells[m_CurrentArchvExcelRowIndex, 6] = pSFZH;
                            //为下一行的写入做好准备索引值
                            m_CurrentArchvExcelRowIndex = m_CurrentArchvExcelRowIndex + 1;
                        }

                        if (dDataReader.IsClosed == false)
                            dDataReader.Close();
                        if (dataReader.IsClosed == false)
                            dataReader.Close();
                    }
                    m_CurrentArchvExcelRowIndex = 2;
                }
              

            }
            catch (Exception ex)
            {
                pFlagOK = false;
                pErrInfo.OccurredException = ex;
                pErrInfo.Content.Append(ex.Message);
                pOccurredException = true;//发生了异常错误
            }
            finally
            {
                //如果处理失败，并且有错误信息那么就把错误信息拼接上该函数的名字标识，便于主程序查找分析异常错误的位置
                if (!pFlagOK && pErrInfo.Content.Length > 0)
                {
                    //获取当前函数名称
                    string className = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName;
                    string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                    StringBuilder tempBuilder = new StringBuilder();
                    //用当前命名空间名.类名.函数名-->·错误信息的格式返回错误描述
                    tempBuilder.Append(className + "." + methodName + "-->");
                    //如果属于异常错误，则需要在错误信息中加入错误位置标记信息
                    if (pOccurredException)
                    {
                        tempBuilder.Append(" ErrPos: ");
                        tempBuilder.Append(pErrPos);
                        tempBuilder.Append(", ");
                    }
                    pErrInfo.Content.Insert(0, tempBuilder.ToString());
                    tempBuilder = null;

                }
            }
            //返回函数处理结果给外部的函数调用者
            return pFlagOK;
        }

 

 

 

         /// <summary>
        /// 向目标Excel表中写入卷内信息
        /// </summary>
        /// <param name="pErrInfo">函数处理失败的错误信息</param>
        /// <returns>如果函数处理成功返回true，否则返回false</returns>
        private bool WriteArchvFiles(ref SqlDataReader dataReader, string pArchvTable, ErrInfo pErrInfo)
        {
            bool pFlagOK = true;  //处理标志，表示是否处理正确
            short pErrPos = 0;
            bool pOccurredException = false;//是否发生了异常错误

            string pDH = "";
            string pBH = "";
            string pCLMC = "";
            string pCLZCSJ = "";
            string pYS = "";
            string pBZ = "";
            ArchvFiles pArchvFiles = null;
            ArchvService pArchvService = new ArchvService(DaoFactory.NewArchvDao(Common.g_DataSource, (DALX)comboBoxDALX.SelectedItem));
            try
            {
                pErrPos = 1;
                string MES = txtBox.Text.Trim();

                if ((MES == null || MES.Length == 0) && (tableFolder.Text == null || tableFolder.Text.Length == 0))
                {
                    pFlagOK = false;
                    pErrInfo.Content.Append("请输入需要生成的报表的流程单号和档号，或者请输入扫描件的文件夹路径");
                    MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }

                if (pFlagOK)
                {
                    pErrPos = 2;
                    ArchvFileService pArchvFileService = new ArchvFileService(DaoFactory.NewArchvFileDao(Common.g_DataSource, ((DALX)comboBoxDALX.SelectedItem).ArchvFileTable));

                    for (int j = 0; j < c.Count; j++)
                    {
                        pArchvFiles = new ArchvFiles();
                        if (pArchvFileService.GetArchvFiles(int.Parse(listViewArchvs.Items[c[j]].SubItems[5].Text), ref dataReader, pArchvTable, pErrInfo) == false)
                        {
                            pFlagOK = false;
                            pErrInfo.Content.Append("获取卷内信息失败。 ");
                            MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        }

                        if (pFlagOK)
                        {
                            DataAccessLib.DataReader dDataReader = new DataAccessLib.DataReader(dataReader);
                            while (dDataReader.Read())
                            {

                                pDH = dDataReader.GetString(Common.g_DALXs[0].DhFieldName);
                                pBH = dDataReader.GetInt32("ParentID").ToString() + "-" + dDataReader.GetInt32("CLXH").ToString();
                                pCLMC = dDataReader.GetString("CLMC");
                                if (dDataReader.GetString("XCSJ").Length == 8)
                                    pCLZCSJ = dDataReader.GetString("XCSJ").Substring(0, 4) + "-" + dDataReader.GetString("XCSJ").Substring(4, 2) + "-" + dDataReader.GetString("XCSJ").Substring(6, 2);
                                if (dDataReader.GetString("XCSJ").Length == 6)
                                    pCLZCSJ = dDataReader.GetString("XCSJ").Substring(0, 4) + "-" + dDataReader.GetString("XCSJ").Substring(4, 2) + "-" + "00";
                                if (dDataReader.GetString("XCSJ").Length == 4)
                                    pCLZCSJ = dDataReader.GetString("XCSJ").Substring(0, 4) + "-" + "00" + "-" + "00";
                                pYS = dDataReader.GetInt32("YS").ToString();
                                pBZ = dDataReader.GetString("BZ");

 

                                //写入数据录入Excel文件
                                //档案编号
                                ws2.Cells[m_CurrentArchvFileExcelRowIndex, 1] = pDH;
                                //编号
                                ws2.Cells[m_CurrentArchvFileExcelRowIndex, 2] = pBH;
                                //材料名称
                                ws2.Cells[m_CurrentArchvFileExcelRowIndex, 3] = pCLMC;
                                //材料制成时间
                                ws2.Cells[m_CurrentArchvFileExcelRowIndex, 4] = pCLZCSJ;
                                //页数
                                ws2.Cells[m_CurrentArchvFileExcelRowIndex, 5] = pYS;
                                //备注
                                ws2.Cells[m_CurrentArchvFileExcelRowIndex, 6] = pBZ;

                                //为下一行的写入做好准备索引值
                                m_CurrentArchvFileExcelRowIndex = m_CurrentArchvFileExcelRowIndex + 1;

                            }
                            if (dDataReader.IsClosed == false)
                                dDataReader.Close();
                            if (dataReader.IsClosed == false)
                                dataReader.Close();
                         

                        }
                        //mtbSCBB.ButtonTextProTitleText = (int)(((float)(j + 1) / (float)pArchvs.Count) * 100f) + "%";
                        mtbSCBB.ButtonTextProTitleText = (j + 1) + "条案卷信息";
                    }
                    m_CurrentArchvFileExcelRowIndex = 2;
                }
            }
            catch (Exception ex)
            {
                pFlagOK = false;
                pErrInfo.OccurredException = ex;
                pErrInfo.Content.Append(ex.Message);
                pOccurredException = true;//发生了异常错误
            }
            finally
            {
                //如果处理失败，并且有错误信息那么就把错误信息拼接上该函数的名字标识，便于主程序查找分析异常错误的位置
                if (!pFlagOK && pErrInfo.Content.Length > 0)
                {
                    //获取当前函数名称
                    string className = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName;
                    string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                    StringBuilder tempBuilder = new StringBuilder();
                    //用当前命名空间名.类名.函数名-->·错误信息的格式返回错误描述
                    tempBuilder.Append(className + "." + methodName + "-->");
                    //如果属于异常错误，则需要在错误信息中加入错误位置标记信息
                    if (pOccurredException)
                    {
                        tempBuilder.Append(" ErrPos: ");
                        tempBuilder.Append(pErrPos);
                        tempBuilder.Append(", ");
                    }
                    pErrInfo.Content.Insert(0, tempBuilder.ToString());
                    tempBuilder = null;

                }
            }
            //返回函数处理结果给外部的函数调用者
            return pFlagOK;
        }