C#导出

C#导出

#region  DataReader 的数据导出到Excle 中
        //public string Exports(string str_sql)
        //{
        //    string destFilePath = "";
        //    Guid guid = Guid.NewGuid();
        //    destFilePath = guid.ToString();
        //    string path = Common.GetServicePath();//返回服务器路径
        //    string serverpath = path + destFilePath + ".xlsx";  //在服务器端定义Excle 让客户端进行进行下载
        //    StreamWriter str_ExcleReader = new StreamWriter(serverpath, false, Encoding.Default);
        //    OracleConnection connection = new OracleConnection(Common.GetConnectStr());
        //    connection.Open();
        //    OracleCommand cmd = new OracleCommand(str_sql, connection);
        //    OracleDataReader Reader = cmd.ExecuteReader();
        //    for (int i = 0; i < Reader.FieldCount; ++i)
        //    {
        //        str_ExcleReader.Write(Reader.GetName(i));
        //        str_ExcleReader.Write('\t');
        //    }
        //    str_ExcleReader.Write("\r\n");
        //    object[] values = new object[Reader.FieldCount];
        //    while (Reader.Read())
        //    {
        //        Reader.GetValues(values);
        //        for (int i = 0; i < values.Length; ++i)
        //        {
        //            str_ExcleReader.Write(values[i].ToString());
        //            str_ExcleReader.Write('\t');
        //        }
        //        str_ExcleReader.Write("\r\n");
        //    }
        //    str_ExcleReader.Flush();
        //    str_ExcleReader.Close();
        //    Reader.Close();
        //    connection.Close();
        //    return ReturnData.WriteXml("<data>" + serverpath + "</data>");
        //}
        #endregion

        #region 根据查询条件查询出的数据导出Excle2010中
        /// <summary>
        ///  根据查询条件把查询出的数据导出Excle2010中
        /// </summary>
        /// <param name="stringSelect"></param>
        public string ExportTO(string defaultFileName)
        {
            string reslut = "true", info = "";
            try
            {

                //注意using的用法；

                //OracleDataReader dataReader = DataReader();
                //ExportToWorkbook(dataReader);
                //info = ExportToWorkbook(dataReader);
                //dataReader.Close();

            }
            catch (Exception ex)
            {
                reslut = "false";
                info = ex.Message;
            }
            return ReturnData.WriteXml("<result>" + reslut + "</result>" + reslut + "<info>" + info + "</info>");
        }
        /// <summary>
        /// 该方法实现dataReader读取的内容导入excel；
        /// </summary>
        /// <param name="dataReader"></param>
        private static string ExportToWorkbook(OracleDataReader dataReader)
        {
            var excelApplication = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                Worksheet workSheet = CreateWorksheet(excelApplication);
                WriteHeader(workSheet, dataReader);
                int rowCount = WriteBody(workSheet, dataReader);
                //格式化表；
                FormatAsTable(workSheet, rowCount, dataReader);
                string destFilePath = "";
                Guid guid = Guid.NewGuid();
                destFilePath = guid.ToString();
                string path = Common.GetServicePath();//返回服务器路径
                string serverpath = path + destFilePath + ".xlsx";  //在服务器端定义Excle 让客户端进行进行下载
                // excelApplication.Save("数据库表.xls");
                excelApplication.Save(serverpath);
                excelApplication.Visible = true;
                return serverpath;
            }
            //保证退出；
            finally
            {
                excelApplication.Quit();
            }
        }

        /// <summary>
        /// 创建工作薄和脚本
        /// </summary>
        /// <param name="excelApplication"></param>
        /// <returns></returns>
        private static Worksheet CreateWorksheet(Microsoft.Office.Interop.Excel.Application excelApplication)
        {
            Workbook workBook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet workSheet = (Worksheet)workBook.Worksheets[1];
            return workSheet;
        }
        /// <summary>
        /// 表的列头
        /// </summary>
        /// <param name="workSheet"></param>
        private static void WriteHeader(Worksheet workSheet, OracleDataReader Reader)
        {
            #region Excle的表头添加数据

            workSheet.Cells[1, 1] = "主编号";
            workSheet.Cells[1, 2] = "次级编号";
            workSheet.Cells[1, 3] = "资产名称";
            workSheet.Cells[1, 4] = "规格型号";
            workSheet.Cells[1, 5] = "技术参数";
            #region 隐藏
            workSheet.Cells[1, 6] = "自编号";
            workSheet.Cells[1, 7] = "资产类别";
            workSheet.Cells[1, 8] = "类别名称";
            workSheet.Cells[1, 1] = "成本中心";
            workSheet.Cells[1, 2] = "资产来源";
            workSheet.Cells[1, 3] = "资产名称";
            workSheet.Cells[1, 4] = "规格型号";
            workSheet.Cells[1, 5] = "技术参数";
            workSheet.Cells[1, 6] = "数量";
            workSheet.Cells[1, 7] = "计量单位";
            workSheet.Cells[1, 8] = "计量单位名称";
            workSheet.Cells[1, 9] = "使用状况";
            workSheet.Cells[1, 10] = "经济用途";
            workSheet.Cells[1, 11] = "技术状态";
            workSheet.Cells[1, 12] = "设计能力";
            workSheet.Cells[1, 13] = "能力单位";
            workSheet.Cells[1, 14] = "生产装置";
            workSheet.Cells[1, 15] = "使用单位";
            workSheet.Cells[1, 16] = "使用单位名称";
            workSheet.Cells[1, 17] = "供应商";
            workSheet.Cells[1, 18] = "供应商名称";
            workSheet.Cells[1, 19] = "生产厂家";
            workSheet.Cells[1, 20] = "生产日期";
            workSheet.Cells[1, 21] = "资本化日期";
            workSheet.Cells[1, 22] = "投产日期";
            workSheet.Cells[1, 23] = "资产原值";
            workSheet.Cells[1, 24] = "累计折旧";
            workSheet.Cells[1, 25] = "减值准备";
            workSheet.Cells[1, 26] = "账面折旧年限";
            workSheet.Cells[1, 27] = "已用年限";
            workSheet.Cells[1, 28] = "原资产编号";
            workSheet.Cells[1, 29] = "原资产次级编号";
            workSheet.Cells[1, 30] = "累计折旧";
            workSheet.Cells[1, 31] = "减值准备";
            #endregion

            #endregion
            for (int i = 0; i < Reader.FieldCount; ++i)
            {
                workSheet.Cells[1, i] = Reader.GetName(i);
            }
        }
        /// <summary>
        /// dataReader内容写入worksheet；
        /// </summary>
        /// <param name="workSheet"></param>
        /// <param name="dataReader"></param>
        /// <returns></returns>
        private static int WriteBody(Worksheet workSheet, OracleDataReader dataReader)
        {
            int rowIndex = 2;
            object[] values = new object[dataReader.FieldCount];
            while (dataReader.Read())
            {
                #region 给定值添加内容
                workSheet.Cells[rowIndex, 1] = dataReader["主编号"];
                workSheet.Cells[rowIndex, 2] = dataReader["次级编号"];
                workSheet.Cells[rowIndex, 3] = dataReader["资产名称"];
                workSheet.Cells[rowIndex, 4] = dataReader["规格型号"];
                workSheet.Cells[rowIndex, 5] = dataReader["技术参数"];
                #region 隐藏
                workSheet.Cells[rowIndex, 6] = dataReader["自编号"];
                workSheet.Cells[rowIndex, 7] = dataReader["资产类别"];
                workSheet.Cells[rowIndex, 8] = dataReader["类别名称"];
                workSheet.Cells[rowIndex, 9] = dataReader["成本中心"];
                workSheet.Cells[rowIndex, 10] = dataReader["资产来源"];
                workSheet.Cells[rowIndex, 11] = dataReader["数量"];
                workSheet.Cells[rowIndex, 12] = dataReader["计量单位"];
                workSheet.Cells[rowIndex, 13] = dataReader["计量单位名称"];
                workSheet.Cells[rowIndex, 14] = dataReader["使用状况"];
                workSheet.Cells[rowIndex, 15] = dataReader["经济用途"];
                workSheet.Cells[rowIndex, 16] = dataReader["技术状态"];
                workSheet.Cells[rowIndex, 17] = dataReader["设计能力"];
                workSheet.Cells[rowIndex, 18] = dataReader["能力单位"];
                workSheet.Cells[rowIndex, 19] = dataReader["生产装置"];
                workSheet.Cells[rowIndex, 20] = dataReader["使用单位"];
                workSheet.Cells[rowIndex, 21] = dataReader["使用单位名称"];
                workSheet.Cells[rowIndex, 22] = dataReader["供应商"];
                workSheet.Cells[rowIndex, 23] = dataReader["生产厂家"];
                workSheet.Cells[rowIndex, 24] = dataReader["生产日期"];
                workSheet.Cells[rowIndex, 25] = dataReader["投产日期"];
                workSheet.Cells[rowIndex, 26] = dataReader["资产原值"];
                workSheet.Cells[rowIndex, 27] = dataReader["累计折旧"];
                workSheet.Cells[rowIndex, 28] = dataReader["减值准备"];
                workSheet.Cells[rowIndex, 29] = dataReader["原资产编号"];
                workSheet.Cells[rowIndex, 30] = dataReader["资产原值"];
                workSheet.Cells[rowIndex, 31] = dataReader["累计折旧"];
                workSheet.Cells[rowIndex, 31] = dataReader["减值准备"];
                #endregion
                rowIndex++;

                #endregion
                dataReader.GetValues(values);
                for (int i = 0; i < values.Length; ++i)
                {
                    workSheet.Cells[rowIndex, i] = dataReader[values[i].ToString()];
                }
            }
            return rowIndex;
        }

        private static void FormatAsTable(Worksheet workSheet, int totalRows, OracleDataReader reader)
        {
            Range topLeft = (Range)workSheet.Cells[1, 1];
            Range bottomRight = (Range)workSheet.Cells.get_Item(totalRows - 1, reader.FieldCount);
            Range range = workSheet.get_Range(topLeft, bottomRight);
            //listObject对象和方法；
            ListObject listObject = workSheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes, null);
            listObject.TableStyle = "TableStyleMedium10";

        }
        #endregion

posted on 2016-01-29 17:07  笨小孩做开发 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/dullbaby/p/5169562.html