比较复杂的导出Excel（2）!!!!!!!!!!

最新推荐文章于 2020-10-10 13:03:58 发布
最新推荐文章于 2020-10-10 13:03:58 发布
阅读量541 收藏
点赞数
分类专栏： 七拼八凑 文章标签： excel dataset string border office alignment
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fenyuxiao/article/details/6291564
版权

[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

     
    }

fenyuxiao
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
jxls使用模板实现导出excel！
07-19
亲自使用的jxls导出功能，使用方法在文档里有说明。。。
C#操作excel
weixin_30307921的博客
12-15 92
//C#读取Excel private void ReadExcel(){ 　　 OleDbCommand cmd = null; OleDbConnection conn = null; string fileName = hidFileName.Text + ".xls"; string filePath = Pa...
如何导出复杂格式的Excel文件？
dadiyang的博客
01-30 2755
前言 我们平时开发会需要做一些 Excel 导出的需求，简单快捷的方式是使用 EasyExcel 来做，如果是简单的数据导出，我们可以在POJO上打ExcelProperty注解，然后直接调用 EasyExcel.write 静态方法，一行代码就搞定了！如： // 定义模型 @Data public class Student { @ExcelProperty(value = "姓名", ...
基于模板excel导出
zhengyangzkr的博客
07-20 1547
本示例中使用了StringTemplate模板技术，进行excel数据导出的操作。      1.首先导入所需要的jar包，本示例中通过maven引入       org.antlr stringtemplate 3.2   2.示例代码如下： package yang.zheng.util.excel; import java.io.BufferedOutputSt
导出excel
woshizou的专栏
03-15 396
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Text; using System.Data; /// ///ExportExcel 的摘要说明 /// public class ExportExcel : System.W
大数据导出Excel方法
lewwaylw的专栏
09-11 926
#region DataSet匯出Excel 文本方法支持大数据         protected void DsTotxt(DataSet ds)         {             string guid = Guid.NewGuid().ToString();             string path = Server.MapPath(string.Format("~
XQL:通过SQL查询Excel文件！
05-23
当前功能： 对加载的文件执行SELECT查询加载多个Excel文件和表格通过加入和合并所有工作表来合并它们未来功能： 编辑功能INSERT和UPDATE查询将查询结果导出并保存到Excel文件随时提出新想法，并通过报告错误和贡献...
java导出excel复杂表头
02-20
java工程，源代码，可直接运行，需自己改造成web下载方式。
Excel报表导出，复杂Excel模板导出（带单元格合并），jxls2
09-12
一个Maven项目，下载直接使用，直接找到 JxlsTest运行demo即可，功能强大，基本能满足所有报表的导出需求，支持多个Sheet导出，支持复杂的导出组合，可以自己写模板试玩下。
java基于模板导出Excel表格
09-14
除了平时简单的数据导出需求外，我们也经常会遇到一些有固定格式或者模板要求的数据导出，这个时候poi导出方式变得复杂且冗长，因此采用EasyExcel的方式进行导出，可以大大提高我们的开发效率和简洁我们的代码。...
Excel模板导出（针对复杂模板情况）
12-14
Excel模板导出（针对复杂模板情况）
java利用FreeMarker导出word
06-18
一个java导出word的完整工程，可直接运行。利用word转换成xml格式，再转换成FreeMarker格式，生成doc文件
poi导出复杂excel
11-21
根据excel模板，拼接出复杂表头（合并行、和并列）的excel，并且能够导出。亲测可以直接运行，内含jar包。如有问题，可私信csdn
Excel导出方法总结篇
weixin_34268169的博客
02-17 166
有时候会时不时碰到这个问题。虽然已经交过作业了，但每次温习居然都有新的感受和发现。于是，再写一篇总结（也不知道是不是以后还会再写总结？)，与大家分享一些所得。我这里简单复习一下有哪几种可以导出Excel的方法：1。把excel作为数据库，导入数据。这里分成两种：一种是把excel作为connection，通过insert数据加入；另一种，则是利用COM对象，调用Excel的QueryTables的...
JAVA导入/导出EXCEL文件，自定义校验，错误回写excel，使用简单快捷
Alan的博客
10-10 5510
JAVA导入/导出EXCEL文件，自定义校验，错误回写excel，使用简单快捷功能介绍IMPORTalanpoi import有何优势？怎么使用alanpoi实现导入一配置一继承一调用Export描叙模式注解模式导出使用高级使用 github地址: https://github.com/alan-et/alanpoi/tree/develop/alanpoi-analysis 项目中使用: <dependency> <groupId>com.alanpoi</g
【GridView表头排序】点击表头可以进行升序和降序的排列
fenyuxiao的专栏
04-20 6120
在.aspx页面GridView绑定的表头中，加入“SortExpression”属性，一般是： 如果有模板列的话，也是一样的：
ext:TextField 不能判断是否输入的是空格解决方法！！！
fenyuxiao的专栏
11-13 5981
ext:TextField 输入全部是空格的一样是可以通过   AllowBlank="false"  的验证保存到数据库中的，必须多加一个属性方法：*公文类别名称" ID="txtTypeName" AllowBlank="false" Width="300" MsgTarget="Side" BlankText="请输入公文类别名称！" MaxLength="50" Validator="
js给FreeTextBox赋值+EXT后台调用赋值方法
fenyuxiao的专栏
07-15 4898
前台FreeTextBox赋值：function SetEditorContent(content)   {        txt_content_editor.document.body.innerText=content//外显值      // alert(document.getElementById("txt_content").value);       } 后台EXT
Ext中ext:CheckboxSelectionModel 的checkbox有时候不会去掉选中的方法
fenyuxiao的专栏
06-22 4442
使用ext:CheckboxSelectionModel 有个时候莫名其妙的不能清空所选中的checkbox，即使在ext:GridPanel设置了SelectionMemory="Disabled" 属性也一样，于是......
poi复杂模板导出excel
最新发布
05-05
2. 创建一个工作簿对象，这是您要导出的Excel文件的主要部分。 ``` Workbook workbook = new XSSFWorkbook(); ``` 3. 创建一个工作表对象，这里我们使用默认名称“Sheet1”。 ``` Sheet sheet = workbook....

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值