比较复杂的导出Excel(2)!!!!!!!!!!

[DirectMethod]
        public void btnExcel_Click(string str)
        {
            DataTable table = new DataTable();
            switch (int.Parse(str))
            {
                case (int)EnumOrderState.Balance:
                    table = serviceBll.SearchOrder_Org(MeasureType.cycle, ServiceDiff.XXT, (int)EnumOrderState.Balance, hid_UserIDList.Text);
                    break;
                case (int)EnumOrderState.Cancel:
                    table = serviceBll.SearchOrder_Org(MeasureType.cycle, ServiceDiff.XXT, (int)EnumOrderState.Cancel, hid_UserIDList.Text);
                    break;
                case (int)EnumOrderState.Pay:
                    table = serviceBll.SearchOrder_Org(MeasureType.cycle, ServiceDiff.XXT, (int)EnumOrderState.Pay, hid_UserIDList.Text);
                    break;
                case (int)EnumOrderState.UnPay:
                    table = serviceBll.SearchOrder_Org(MeasureType.cycle, ServiceDiff.XXT, (int)EnumOrderState.UnPay, hid_UserIDList.Text);
                    break;
                           }

            try
            {
            DataSet ds = new DataSet();

            ds.Tables.Add(ExcelOrderBind(table));//将table的数据转换为需要的数据形式
                string rand = DateTime.Now.ToString("HHmmss");
                string fileName = string.Format("订单信息{0}.xls", rand);
               ExportExcel.ImportInToExcel(fileName, ds);
            }
            catch (Exception ex)
            {
                ShowErrorMessage("温馨提示", ex.Message);
            }

        }

        public DataTable ExcelOrderBind(DataTable table)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("订单号 ");
            dt.Columns.Add("家长手机");
            dt.Columns.Add("家长/学生");
            dt.Columns.Add("产品名称");
            dt.Columns.Add("订单金额");
            dt.Columns.Add("已付金额");
            dt.Columns.Add("下单时间");
            dt.Columns.Add("状态");

            foreach (DataRow dr in table.Rows)
            {
                DataRow row = dt.NewRow();
                row["订单号 "] = dr["OrderNumber"];
                row["家长手机"] = dr["buyertelphone"];
                row["家长/学生"] = dr["buyername"];
                row["产品名称"] = dr["productname"];
                row["订单金额"] = dr["TotalMoney"];
                row["已付金额"] = dr["LeaveBalance"];
                row["下单时间"] = dr["AddOrderTime"];
                switch (int.Parse(dr["OrderState"].ToString()))
                {
                    case (int)EnumOrderState.Balance:
                        row["状态"] = "未完全支付订单";
                        break;
                    case (int)EnumOrderState.Cancel:
                        row["状态"] = "已取消订单";
                        break;
                    case (int)EnumOrderState.Exception:
                        row["状态"] = "订单异常";
                        break;
                    case (int)EnumOrderState.Pay:
                        row["状态"] = "已支付订单";
                        break;
                    case (int)EnumOrderState.UnPay:
                        row["状态"] = "未支付订单";
                        break;

                }
                dt.Rows.Add(row);

            }

            return dt;
        }

 

 

--------------------------------------------------------------------------------------------------------------------

类中方法:

 

 /// <summary>
    /// 数据快速导入Excel
    /// </summary>
    public class ExportExcel
    {

        #region 从DataSet导入Excel [快速导入]
        /// <summary>
        /// 从DataSet导入Excel [快速导入]
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <returns></returns>
        public static string ImportExcel(DataSet ds)
        {
            try
            {
                //将要生成的Excel文件               
                StringBuilder writer = new StringBuilder();
                writer.AppendLine("<?xml version=/"1.0/"?>");
                writer.AppendLine("<?mso-application progid=/"Excel.Sheet/"?>");
                //Excel工作薄开始
                writer.AppendLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"");
                writer.AppendLine(" xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
                writer.AppendLine(" xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
                writer.AppendLine(" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"");
                writer.AppendLine(" xmlns:html=/"http://www.w3.org/TR/REC-html40//">");
                writer.AppendLine(" <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
                writer.AppendLine(" <Author>Mrluo735</Author>");
                writer.AppendLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
                writer.AppendLine(" <Company>Mrluo735</Company>");
                writer.AppendLine(" <Version>11.6408</Version>");
                writer.AppendLine(" </DocumentProperties>");
                writer.AppendLine(" <ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">");
                writer.AppendLine("  <WindowHeight>8955</WindowHeight>");
                writer.AppendLine("  <WindowWidth>11355</WindowWidth>");
                writer.AppendLine("  <WindowTopX>480</WindowTopX>");
                writer.AppendLine("  <WindowTopY>15</WindowTopY>");
                writer.AppendLine("  <ProtectStructure>False</ProtectStructure>");
                writer.AppendLine("  <ProtectWindows>False</ProtectWindows>");
                writer.AppendLine(" </ExcelWorkbook>");
                //Excel工作薄结束
                //工作薄样式
                writer.AppendLine("<Styles>");
                writer.AppendLine("<Style ss:ID=/"Default/" ss:Name=/"Normal/">");
                writer.AppendLine("  <Alignment ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
                writer.AppendLine("  <Borders>");
                writer.AppendLine("      <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("      <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("      <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
                writer.AppendLine("      <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("  </Borders>");
                writer.AppendLine(string.Format("<Font ss:FontName=/"{0}/" x:CharSet=/"{1}/" ss:Size=/"{2}/"/>", "宋体", 134, 12));
                writer.AppendLine("  <Interior/>");
                writer.AppendLine("  <Protection/>");
                writer.AppendLine("</Style>");
                writer.AppendLine("  <Style ss:ID=/"s21/">");
                writer.AppendLine("   <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
                writer.AppendLine("  </Style>");
                writer.AppendLine("<Style ss:ID=/"BoldColumn/">");
                writer.AppendLine("  <Font ss:FontName=/"宋体/" ss:Bold=/"1/"/>");
                writer.AppendLine("  <Borders>");
                writer.AppendLine("      <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("      <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("      <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("      <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                writer.AppendLine("  </Borders>");
                writer.AppendLine("</Style>");
                //文本样式
                writer.AppendLine("<Style ss:ID=/"StringLiteral/">");
                writer.AppendLine("  <NumberFormat ss:Format=/"@/"/>");
                writer.AppendLine("</Style>");
                //浮点型样式
                writer.AppendLine("<Style ss:ID=/"Decimal/">");
                writer.AppendLine("  <NumberFormat ss:Format=/"0.00/"/>");
                writer.AppendLine("</Style>");
                //整型样式
                writer.AppendLine("<Style ss:ID=/"Integer/">");
                writer.AppendLine("  <NumberFormat ss:Format=/"0/"/>");
                writer.AppendLine("</Style>");
                //日期样式
                writer.AppendLine("<Style ss:ID=/"DateLiteral/">");
                writer.AppendLine("  <NumberFormat ss:Format=/"mm/dd/yyyy;@/"/>");
                writer.AppendLine("</Style>");
                writer.AppendLine(" </Styles>");
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    int rows = ds.Tables[i].Rows.Count + 1;
                    int cols = ds.Tables[i].Columns.Count;
                    //第i个工作表
                    writer.AppendLine(string.Format("<Worksheet ss:Name=/"{0}/">", ds.Tables[i].TableName));
                    writer.AppendLine(string.Format("    <Table ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"", cols.ToString(), rows.ToString()));
                    writer.AppendLine("   x:FullRows=/"1/">");
                    //ExpandedColumnCount:代表Excel文档中的列数
                    //ExpandedRowCount:代表Excel文档中的行数

                    //指定每一列的宽度
                    for (int c = 0; c < ds.Tables[i].Columns.Count; c++)
                    {
                        writer.AppendLine(string.Format("<Column ss:Index=/"{0}/" ss:AutoFitWidth=/"{1}/" ss:Width=/"{2}/"/> ", c + 1, 1, 80));
                    }
                    //生成标题
                    writer.AppendLine(string.Format("<Row ss:AutoFitHeight=/"{0}/" ss:Height=/"{1}/">", 0, 28.5));
                    foreach (DataColumn eachCloumn in ds.Tables[i].Columns)
                    {
                        writer.Append("<Cell ss:StyleID=/"s21/"><Data ss:Type=/"String/">");
                        writer.Append(eachCloumn.ColumnName.ToString());
                        writer.AppendLine("</Data></Cell>");
                    }
                    writer.AppendLine("</Row>");

                    //生成数据记录
                    foreach (DataRow eachRow in ds.Tables[i].Rows)
                    {
                        writer.AppendLine("<Row ss:AutoFitHeight=/"0/">");
                        for (int currentRow = 0; currentRow != cols; currentRow++)
                        {
                            object[] getValue = ExcelContent(eachRow[currentRow]);

                            writer.Append(string.Format("<Cell ss:StyleID=/"{0}/"><Data ss:Type=/"{1}/">", getValue[0], getValue[1]));
                            writer.Append(getValue[2]);
                            writer.AppendLine("</Data></Cell>");
                        }
                        writer.AppendLine("</Row>");
                    }
                    writer.AppendLine("</Table>");
                    writer.AppendLine("<WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
                    writer.AppendLine("<Selected/>");
                    writer.AppendLine("<Panes>");
                    writer.AppendLine("<Pane>");
                    writer.AppendLine("  <Number>3</Number>");
                    writer.AppendLine("  <ActiveRow>1</ActiveRow>");
                    writer.AppendLine("</Pane>");
                    writer.AppendLine("</Panes>");
                    writer.AppendLine("<ProtectObjects>False</ProtectObjects>");
                    writer.AppendLine("<ProtectScenarios>False</ProtectScenarios>");
                    writer.AppendLine("</WorksheetOptions>");
                    writer.AppendLine("</Worksheet>");
                }
                writer.AppendLine("</Workbook>");

                return writer.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据C#值把它转换成Excel值 [ExcelContent(...)]
        /// <summary>
        /// 根据C#值把它转换成Excel值 [ExcelContent(...)]
        /// </summary>
        /// <param name="Value">值</param>
        /// <returns>Excel样式,数据类型,文本</returns>
        private static object[] ExcelContent(object Value)
        {
            object[] strValue = new object[3];
            System.Type rowType = Value.GetType();
            switch (rowType.ToString())
            {
                case "System.String":
                case "System.Guid":
                    string XMLstring = Value.ToString();
                    XMLstring = XMLstring.Trim();
                    XMLstring = XMLstring.Replace("&", "&");
                    XMLstring = XMLstring.Replace(">", ">");
                    XMLstring = XMLstring.Replace("<", "<");
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = XMLstring;
                    break;
                case "System.DateTime":
                    DateTime XMLDate = (DateTime)Value;
                    string XMLDatetoString = ""; //Excel Converted Date
                    //把日期时间转化为:“yyyy-MM-ddTHH:mm:ss”这种Excel中的格式
                    XMLDatetoString = XMLDate.ToString(System.Globalization.DateTimeFormatInfo.CurrentInfo.SortableDateTimePattern);
                    strValue[0] = "DateLiteral";
                    strValue[1] = "DateTime";
                    if (XMLDate < Convert.ToDateTime("1900-1-1"))
                    {
                        strValue[0] = "StringLiteral";
                        strValue[1] = "String";
                        XMLDatetoString = string.Empty;
                    }
                    strValue[2] = XMLDatetoString;
                    break;
                case "System.Boolean":
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = Value.ToString();
                    break;
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    strValue[0] = "Integer";
                    strValue[1] = "Number";
                    strValue[2] = Value.ToString();
                    break;
                case "System.Byte[]":
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = (byte[])Value;
                    break;
                case "System.Decimal":
                case "System.Double":
                    strValue[0] = "Decimal";
                    strValue[1] = "Number";
                    strValue[2] = Value.ToString();
                    break;
                case "System.DBNull":
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = "";
                    break;
                default:
                    throw (new Exception(rowType.ToString() + " not handled."));
            }
            return strValue;
        }
        #endregion

        #region  从DataSet导入Excel [快速导入](ds)
        public static void ImportInToExcel(string fileName, DataSet ds)
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel; charset=utf-8";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            HttpContext.Current.Response.Write(ImportExcel(ds));
            HttpContext.Current.Response.End();
        }
        #endregion

     
    }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值