by ·0评论
C# .NET Hallo world

要解决中文乱码问题，首先我们需要知道原因，一开始我尝试了gb2312，utf-8等多种编码格式导出，使用 Execl 打开均为乱码，后来无意中发现，必须为“带 BOM 的 UTF-8”。

代码示例：

using(var sw = new File.CreateText(fileName))
{
	sw .WriteLine(csvContent);
}

如果是asp.net页面输出，则为：

Response.Clear();
Response.Buffer = true;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToShortDateString() + fileName + ".xls");
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.Charset = "utf-8";
Response.ContentType = "text/csv";
using(var outputWriter = new StreamWriter(Response.OutputStream, System.Text.Encoding.UTF8))
{
	outputWriter.WriteLine(csvContent);
}
Response.Flush();
Response.End();

by ·0评论

C# 读取 CSV 文件数据到 DataTable。

public static DataTable ReadCSVtoDataTable(string fileName)
{
	DataTable dt = new DataTable();
	Regex regex = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
	using (StreamReader sr = new StreamReader(fileName))
	{
		string[] headers = sr.ReadLine().Split(',');
		foreach (string header in headers)
		{
			dt.Columns.Add(header);
		}
		while (sr.Peek() > 0)
		{
			string[] rows = regex.Split(sr.ReadLine());
			DataRow dr = dt.NewRow();
			for (int i = 0; i < headers.Length; i++)
			{
				dr[i] = rows[i];
			}
			dt.Rows.Add(dr);
		}
	}
	return dt;
}

by ·0评论

DataTable 导出 CSV，支持.NET Framework 4.0以上。

public byte[] DataTableToCSV(DataTable data)
{
	StringBuilder sb = new StringBuilder();
	IEnumerable<string> columnNames = data.Columns.Cast<DataColumn>().
									  Select(column => column.ColumnName);
	sb.AppendLine(string.Join(",", columnNames));
	foreach (DataRow row in data.Rows)
	{
		IEnumerable<string> fields = row.ItemArray.Select(field =>
		  string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
		sb.AppendLine(string.Join(",", fields));
	}
	return System.Text.Encoding.Default.GetBytes(sb.ToString());
}

by ·0评论

试用Spire.Xls读取Execl文件或IO，导出到 DataTable。

public DataTable ReadExecl(string fileName)
{
	using (var workbook = new Spire.Xls.Workbook())
	{
		workbook.LoadFromFile(fileName);
		using (var sheet = workbook.Worksheets[0])
		{
			return sheet.ExportDataTable();
		}
	}
}

by ·0评论
//源Execl名称
string sourceExeclFileName = "C:\\SourceExecl.xlsx";
//目标Execl名称
string targetExeclFileName = "C:\\TargetExecl.xlsx";
using (ExcelPackage package = new ExcelPackage(new FileInfo(targetExeclFileName)))
{
	using (ExcelPackage sourcePackage = new ExcelPackage(new FileInfo(sourceExeclFileName)))
	{
		//new sheet form copy为复制到目标Execl的SheetName
		package.Workbook.Worksheets.Add("new sheet from copy", sourcePackage.Workbook.Worksheets[1]);
	}
	package.Save();
}

by ·0评论

利用 SheetJS js-xlsx 读取 Execl 数据转为JSON

<script type="text/javascript" src="Script/jquery.min.js"></script>
<script type="text/javascript" src="Script/js-xlsx/shim.min.js"></script>
<script type="text/javascript" src="Script/js-xlsx/xlsx.full.min.js"></script>
<script type="text/javascript">
	$(function () {
		$('.file-upload').change(function () {
			var $self = $(this);
			if (!this.files.length) {
				return;
			}
			//利用 FileReader 读取用户选中的Execl文件
			var reader = new FileReader();
			reader.onload = function (e) {
				var data = new Uint8Array(e.target.result);
				var workbook = XLSX.read(data, { type: 'array' });
				//获取第一个 Sheet 名称
				var sheetName = workbook.SheetNames[0];
				//获取第一个 Sheet
				var sheet = workbook.Sheets[sheetName];
				//转换为JSON格式数组
				var sheetJSONData = XLSX.utils.sheet_to_json(sheet);
			};
			reader.readAsArrayBuffer(this.files[0]);
		});
	});
</script>

相关文件下载

js-xlsx-0.14.0.zip

by ·0评论
public sealed class AcrobatUtil
{
	public string ConvertToExecl(string inputFileName, string outputFileName)
	{
		//判断文件是否存在
		if (!File.Exists(inputFileName))
		{
			return "File Is Missing";
		}
		try
		{
			//判断是否已经安装Acrobat
			string acrobatPath = GetAppPath();
			if (acrobatPath == null)
			{
				return "Adobe Acrobat not installed.";
			}

			var aVDoc = new AcroAVDoc();
			aVDoc.Open(inputFileName, "");
			CAcroPDDoc PDDoc = (CAcroPDDoc)aVDoc.GetPDDoc();
			object jsObj = PDDoc.GetJSObject();
			var jsObjType = jsObj.GetType();
			//另存为
			jsObjType.InvokeMember(
							"saveAs",
							BindingFlags.InvokeMethod |
							BindingFlags.Public |
							BindingFlags.Instance,
							null, jsObj, new object[] { outputFileName, "com.adobe.acrobat.xlsx" });
/*
另存为更多格式
html htm      com.adobe.acrobat.html
jpeg jpg      com.adobe.acrobat.jpeg
docx          com.adobe.acrobat.docx
doc           com.adobe.acrobat.doc
png           com.adobe.acrobat.png
ps            com.adobe.acrobat.ps
rft           com.adobe.acrobat.rft
xlsx          com.adobe.acrobat.xlsx
xls           com.adobe.acrobat.spreadsheet
txt           com.adobe.acrobat.accesstext
tiff tif      com.adobe.acrobat.tiff
xml           com.adobe.acrobat.xml-1-00
*/
			//关闭文档
			aVDoc.Close(1);
		}
		catch (Exception ex)
		{
			return "Error:" + ex.Message;
		}
		return null;
	}
	
	//获取Acrobat安装路径
	private string GetAppPath()
	{
		string acrobatPath = null;
		using (RegistryKey acrobatRegistryKey = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Adobe\Adobe Acrobat"))
		{
			if (acrobatRegistryKey == null)
			{
				return acrobatPath;
			}
			string acrobatVersion = acrobatRegistryKey.GetValue("TrackName").ToString();
			using (RegistryKey acrobatInstallPathRegistryKey = acrobatRegistryKey.OpenSubKey(acrobatVersion + @"\InstallPath"))
			{
				if (acrobatInstallPathRegistryKey == null)
				{
					return acrobatPath;
				}
				acrobatPath = acrobatInstallPathRegistryKey.GetValue("").ToString() + @"\Acrobat.exe";
			}
		}
		return acrobatPath;
	}
}