DataTable2Excel无乱码(UTF8)

最新推荐文章于 2022-08-30 16:54:38 发布
最新推荐文章于 2022-08-30 16:54:38 发布
阅读量1.1k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： asp.net 文章标签： excel string path null table header
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/houshen/article/details/4633509
本文介绍了一个用于将DataTable数据导出到Excel的方法，该方法能够处理中文字符不出现乱码的问题，并允许自定义文件名、标题、字体样式等。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

public class ExportToExcel
    {
        #region 成员变量声明
        private DataTable _exportData;
        private string _filename = "ExportData.xls";
        private string _title = "";
        private string _extraInfo = "";
        private string title_FontName = "Arial,sans-serif";
        private string title_FontSize = "12";   //Pt
        private bool title_FontBold = true;     //默认粗体
        private string _dateTimeFormate = "{0:d}"; //默认日期格式{0:d}
        private const string C_HTTP_HEADER_CONTENT = "Content-Disposition";
        private const string C_HTTP_ATTACHMENT = "attachment;filename=";
        private const string C_HTTP_INLINE = "inline;filename=";
        private const string C_HTTP_CONTENT_TYPE_EXCEL = "application/ms-excel";
        private const string C_HTTP_CONTENT_LENGTH = "Content-Length";
        private const string C_ERROR_NO_RESULT = "Data not found.";
        private const string C_TABLE_START = "<TABLE cellSpacing=1 cellPadding=1   border=1>";
        private const string C_TABLE_END = "</TABLE>";
        #endregion 成员变量声明
        #region 属性
        /// <summary>
        /// 获取或设置要导出的数据源
        /// </summary>
        public DataTable Datatable
        {
            get
            {
                return this._exportData;
            }
            set
            {
                this._exportData = value;
            }
        }

        /// <summary>
        /// 获取或设置导出的文件名称
        /// </summary>
        public string FileNameToExport
        {
            get
            {
                return this._filename;
            }
            set
            {
                if (value != null && value.Trim() != "")
                {
                    if (value.IndexOf(".xls") < 1)
                        this._filename = HttpUtility.UrlEncode(value + ".xls", Encoding.UTF8);
                    else
                        this._filename = HttpUtility.UrlEncode(value, Encoding.UTF8);
                }
            }
        }
        /// <summary>
        /// 获取或设置标题字体
        /// 默认宋体
        /// </summary>
        public string Title_FontName
        {
            get
            {
                return this.title_FontName;
            }
            set
            {
                this.title_FontName = value;
            }
        }
        /// <summary>
        /// 获取或设置标题字体大小
        /// 默认14pt
        /// </summary>
        public string Title_FontSize
        {
            get
            {
                return this.title_FontSize;
            }
            set
            {
                this.title_FontSize = value;
            }
        }
        /// <summary>
        /// 获取或设置标题字体是否粗体
        /// 默认为粗体
        /// </summary>
        public bool Title_FontBold
        {
            get
            {
                return this.title_FontBold;
            }
            set
            {
                this.title_FontBold = value;
            }
        }
        /// <summary>
        /// 获取或设置标题
        /// </summary>
        public string Title
        {
            get
            {
                return this._title;
            }
            set
            {
                if (value != null)
                    this._title = value;
            }
        }
        /// <summary>
        /// 获取或设置除标题外其他的额外信息
        /// </summary>
        public string ExtraInfo
        {
            get
            {
                return this._extraInfo;
            }
            set
            {
                if (value != null)
                    this._extraInfo = value;
            }
        }
        /// <summary>
        /// 获取或设置日期格式
        /// 默认{0:d}
        /// </summary>
        public string DateTimeFormate
        {
            get
            {
                return this._dateTimeFormate;
            }
            set
            {
                this._dateTimeFormate = value;
            }
        }
        #endregion 属性
        #region DataTable导入到Excel(无乱码)
        public void Export(string fileName)
        {
            if (fileName.Trim().Length > 0)
            {
                FileNameToExport = fileName;
            }
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.AddHeader(C_HTTP_HEADER_CONTENT, C_HTTP_ATTACHMENT + this._filename);
            response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL;
            Encoding encoding = Encoding.UTF8;
            response.ContentEncoding = encoding;
            response.Charset = "UTF-8";
            string upLoadPath = HttpContext.Current.Server.MapPath(@"~/UploadFiles/Temp/");
            string path = upLoadPath + Guid.NewGuid().ToString() + System.IO.Path.GetExtension(this._filename);
            System.IO.StreamWriter sw = System.IO.File.CreateText(path);
            if (Datatable != null && Datatable.Rows.Count > 0)
                writeTable(Datatable, sw);
            sw.Close();
            DownFile(response, fileName, path);
            response.End();
            response.Close();
        }
        public void Export(DataTable srcDataTable, string fileName)
        {
            if (fileName.Trim().Length > 0)
            {
                FileNameToExport = fileName;
            }
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.AddHeader(C_HTTP_HEADER_CONTENT, C_HTTP_ATTACHMENT + this._filename);
            response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL;
            Encoding encoding = Encoding.UTF8;
            response.ContentEncoding = encoding;
            response.Charset = "UTF-8";
            string upLoadPath = HttpContext.Current.Server.MapPath(@"~/UploadFiles/Temp/");
            string path = upLoadPath + Guid.NewGuid().ToString() + System.IO.Path.GetExtension(this._filename);
            System.IO.StreamWriter sw = System.IO.File.CreateText(path);
            if (srcDataTable != null && srcDataTable.Rows.Count > 0)
                writeTable(srcDataTable,sw);
            sw.Close();
            DownFile(response, fileName, path);
            response.End();
            response.Close();
        }
        private bool DownFile(System.Web.HttpResponse Response, string fileName, string path)
        {
            try
            {
                Response.ContentType = "application/octet-stream";
                //Response.ContentType = "application/vnd.ms-excel";
                Response.Buffer = true;
                Response.AppendHeader("Content-Disposition", "attachment;filename=" +
                HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=UTF-8");
                StreamReader sr = new StreamReader(path, Encoding.UTF8, true);
                int size = 20480;
                string readStr = string.Empty;
                while (!sr.EndOfStream)
                {
                    while (readStr.Length < size && !sr.EndOfStream)
                    {
                        readStr += sr.ReadLine();
                    }
                    int len = Encoding.UTF8.GetByteCount(readStr);
                    len = len + readStr.Length;
                    if (len < 1024) len = 1024;
                    byte[] buffer = new byte[len];
                    Encoding.UTF8.GetBytes(readStr, 0, readStr.Length, buffer, 0);
                    readStr = string.Empty;
                    Response.BinaryWrite(buffer);
                }
                sr.Close();
                System.IO.File.Delete(path);
                return true;
            }
            catch
            {
                return false;
            }
        }
        private void writeTable(DataTable srcData,StreamWriter sw)
        {
            int ColumnCount = srcData.Columns.Count;
            int RowCount = srcData.Rows.Count;
            sw.WriteLine(C_TABLE_START);
            //插入标题
            //if (this._title == "")
            //    this._title = srcData.TableName;
            if (this._title != "")
            {
                sw.WriteLine("<TR><TD HEIGHT=40 colSpan=" + ColumnCount + " align=center style=/"FONT-WEIGHT: " + ((this.title_FontBold) ? Convert.ToString("bold") : Convert.ToString("normal")) + "; FONT-SIZE: " + this.title_FontSize + "pt; FONT-FAMILY: " + this.title_FontName + "/">" + _title + "</TD></TR>");
            }
            //插入额外信息
            if (this._extraInfo != "")
            {
                sw.WriteLine("<TR><TD colSpan=" + ColumnCount + ">" + _extraInfo + "</TD></TR>");
                sw.WriteLine("<BR>");
            }
            sw.WriteLine("<TR>");
            //插入列标题
            foreach (DataColumn aCol in srcData.Columns)
            {
                sw.WriteLine("<TD align=center style=/"FONT-WEIGHT: bold/">" + this.ToExcelStr(aCol.Caption) + "</TD>");
            }
            sw.WriteLine("</TR>");
            //插入明细内容
            foreach (DataRow aRow in srcData.Rows)
            {
                sw.WriteLine("<TR>");
                foreach (DataColumn aCol in srcData.Columns)
                {
                    object obj = aRow[aCol.ColumnName];
                    if (aCol.DataType.FullName == "System.String")
                        //下面一句的Style属性设置所有的数字都为字符串格式，即身份证能正常显示
                        sw.WriteLine("<TD style='vnd.ms-excel.numberformat:@'>" + ((obj == null) ? " " : this.ToExcelStr(obj.ToString())) + "</TD>");
                    else if (aCol.DataType.FullName == "System.Int32")
                        sw.WriteLine("<TD style='align:right'>" + ((obj == null) ? " " : this.ToExcelStr(obj.ToString())) + "</TD>");
                    else if (aCol.DataType.FullName == "System.DateTime")
                    {
                        sw.WriteLine("<TD style='vnd.ms-excel.numberformat:@'>" + ((obj == null) ? " " : this.ToExcelStr(string.Format(DateTimeFormate, obj))) + "</TD>");
                    }
                    else sw.WriteLine("<TD>" + ((obj == null) ? " " : this.ToExcelStr(obj.ToString())) + "</TD>");
                }
                sw.WriteLine("</TR>");
            }
            sw.WriteLine(C_TABLE_END);
        }
      
        private string ToExcelStr(string srcStr)
        {
            if (srcStr == null || srcStr == "")
                return "";
            else
            {
                string rtnStr = srcStr.Replace(Environment.NewLine, "");
                rtnStr = rtnStr.Replace(" ", " ");
                rtnStr = rtnStr.Replace("<", "＜");
                rtnStr = rtnStr.Replace(">", "＞");
                rtnStr = rtnStr.Replace("/n", "");
                rtnStr = rtnStr.Replace("/r", "");
                return rtnStr;
            }
        }
        #endregion
    }
csv datatable 乱码 导出_DataTable内容导出为CSV文件
weixin_32165903的博客
12-24 190
1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Web;6 usingSystem.Threading;7 usingSystem.IO;8 usingSystem.Data;9 usingSystem.Windows.Forms;1011 nam...
C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文文件名乱码
热门推荐
kogu的博客
10-18 1万+
本文原创地址：http://blog.youkuaiyun.com/ranbolwb/article/details/8083983 ，转载请保留本行。 本例子是上一篇 DataTable 导出 Excel 的进阶，除了上一篇提到的处理乱码问题，本例还添加了处理多行表头、合并单元格的功能及处理中文文件名乱码问题，应该可以满足日常开发的需要了。 废话不多说了，直接上代码： using System;
jquery.table2excel
11-21
可以很方便的将页面上的table导出excal文件，配置简单。
Excel文件导入DataTable(2) -- 直接打开Excel文件
weixin_30894389的博客
08-27 175
通过OleDb导入方便很多，但是有时候我们想直接操作Excel文件。使用Excel的Com组件。首先导入Microsoft.Office.Interop.Excel，我的是Office2007，对应版本号为12.0.0.0再添加命名空间：using System.Data;using Excel = Microsoft.Office.Interop.Excel;其余部分代码如下： ReadExc...
DataTable2Excel网上找的 原文出处不详
andy930606的博客
11-01 81
public static voidDataTable2Excel(DataTabledtData, String FileName) { System.Web.UI.WebControls.GridView dgExport = null; //当前对话 Sys...
html页面的table转excel 支持中文 jquery插件
09-12
html页面的table转excel 支持中文 jquery插件
table2excel表格导出带样式
weixin_47058581的博客
07-03 1479
总结一下前端excel导出 if (cell.tagName === ‘TH’ && !cellObject.s) { cellObject.s = { font: { bold: true },//字体 fill:{ fgColor:{rgb:‘c6c6c6’},bgColor:{rgb:‘c6c6c6’} },//背景色 alignment:{ vertical:‘center’, horizontal:‘center’, wrapText:false }, border:{
UE 导入DataTable中文乱码问题
linglongbayinhe的博客
08-30 1163
UE导入DataTable中文乱码问题
NPOI实现Excel与DataTable高效转换方法
- 使用NPOI时，还需要注意文件的编码格式，如UTF-8或ANSI，确保读取文件时不会出现乱码。 最后，NPOI是一个持续更新和维护的项目，开发者可以关注其官方文档和社区讨论，以便获取最新的使用技巧和解决问题的方法。...
Unreal Excel表格导入和使用（中文乱码问题）
妖刀丶朔夜
11-09 2893
将Excel表格转为CSV文件
tableExport.js excel乱码解决方法
08-25
亲测可用,已加到项目里了,本想免费,但找到正确方法也耗了不少时间,所以收5分不过分吧? ----------------------------- 自从上次用bootstrap table就一直没有解决导出excel的关题,网上找到的那更改tableExport.js和jquery.base64.js的方法虽然可以正常导出,但中文依旧乱码... 解决方法终归是有的....现将文件分享给大家. 里面有tableExport.js和jquery.base64.js两个js文件,但只要引入tableExport就可以导出了.
解决tableExport导出到excel中文乱码
08-03
解决tableExport导出到excel中文乱码，解决tableExport导出到excel中文乱码，解决tableExport导出到excel中文乱码，解决tableExport导出到excel中文乱码
JSP转EXCEL时出现中文乱码
来一杯吧
04-01 420
点击查询是jsp显示，导出是EXCEL内容，jsp显示正常，EXCEL内容出现了乱码 String strOutType=request.getParameter("OutputType"); //得到报表数据的输出类型 System.out.println("得到报表数据的输出类型 =====strOutType:"+strOutType); if(strOutType!=nu...
table2excel导出Excel，科学计数法显示的问题(针对office)
MakeMeCry的专栏
07-27 1457
直接把HTML里的TABLE导出excel，默认会把过长的数字类型转成科学计数法，这在导出身份证号这种信息的时候非常麻烦。 虽然可以在数字前面加上一个符号，比如'，这样就会自动变成文本形式了。但是一排带着单引号的身份证号看着还是很别扭，尤其是强迫症患者。当然，加一些透明或隐藏符号也行。不过有时候没法这么做，后续对EXCEL操作可能出问题。 这时候就可以在导出的TABLE的TD标签加上一个样式，比如<td style="vnd.ms-excel.numberformat:@">11111111
使用jquery.table2excel.js导出页面列表为excel表格
zb27149的博客
02-28 1万+
最近做项目遇到了列表导出功能，发挥程序员不懂就搜的风格，找到一款插件jquery.table2excel.js； 使用时只需将该文件下载到本地项目目录，链接完成即可； 配置方式如下，给导出按钮添加点击事件：function export_list(){ $("#xxxxxx").table2excel({ // exclude CSS class
vue中把数据导出为Excel
weixin_45102492的博客
01-11 545
在做项目中遇见了这样一个需求，把表数据导出为Excel，百度了大半天，好多都是需要修改发送请求的响应数据类型，很麻烦。偶然间发现了一款好用的数据导出组件，只需要把表数据以数组的形式传递过去就可以了，官网地址：https://www.npmjs.com/package/xlsx 实现步骤如下： 安装相应的依赖库 npm install -s xlsx 创建一个工具类进行方法的封装，以便于其他地方的调用。我创建的文件名叫data2Excel.js import XLSX,{ WorkSheet } from
datatable添加中文支持
yiluohan0307的专栏
05-07 1645
datatable添加中文支持
用c#读取Excel的方法
houshen的专栏
10-05 1988
一、 在工程中加入相关的Com组件 代码示例:    ExcelObj=new Excel.Application();     object missing=Type.Missing;     Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(      this.text
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值