Files
GX.O3.WMS/WebApi/.svn/pristine/a3/a30a998ecf7403b215e132e9400941ebba402c8d.svn-base

307 lines
14 KiB
Plaintext
Executable File
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Xml;
namespace GXWebApi.Common
{
/// <summary>
/// Excel 导入/导出公共方法
/// 支持 .xls(XML Spreadsheet 2003)和 .xlsx(OpenXML)格式的读取
/// 导出使用 HTML table 格式(Excel 可直接打开)
/// </summary>
public static class ExcelHelper
{
/// <summary>
/// 读取Excel行数据(自动识别 .xls / .xlsx)
/// </summary>
/// <param name="fileBytes">文件字节</param>
/// <param name="fileName">文件名(用于判断格式)</param>
/// <returns>二维字符串数组,第一行为表头</returns>
public static List<string[]> ReadRows(byte[] fileBytes, string fileName)
{
if (string.IsNullOrEmpty(fileName) || fileName.ToLower().EndsWith(".xlsx"))
return ReadXlsxRows(fileBytes);
return ReadXlsXmlRows(fileBytes);
}
/// <summary>
/// 读取 .xls(XML Spreadsheet 2003)格式
/// 处理空单元格缺失问题(ss:Index 补齐)
/// </summary>
public static List<string[]> ReadXlsXmlRows(byte[] fileBytes)
{
if (fileBytes == null || fileBytes.Length < 8)
throw new InvalidDataException("文件内容无效");
// 按真实文件头自动识别格式:
// PK\x03\x04 -> .xlsx(OpenXML ZIP)
// D0 CF 11 E0 A1 B1 1A E1 -> 二进制 .xls(OLE2 旧版 Excel)
// 其他 -> Excel 2003 XML 格式
if (fileBytes[0] == 0x50 && fileBytes[1] == 0x4B)
return ReadXlsxRows(fileBytes);
if (fileBytes[0] == 0xD0 && fileBytes[1] == 0xCF && fileBytes[2] == 0x11 && fileBytes[3] == 0xE0)
return ReadXlsBinaryRows(fileBytes);
List<string[]> rows = new List<string[]>();
XmlDocument doc = new XmlDocument();
using (var ms = new MemoryStream(fileBytes)) doc.Load(ms);
var ns = new XmlNamespaceManager(doc.NameTable);
ns.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var rowNodes = doc.SelectNodes("//ss:Row", ns);
// 先扫描所有行,确定最大列数(用于统一补齐)
int maxCols = 0;
if (rowNodes != null)
{
foreach (XmlNode rowNode in rowNodes)
{
var cellNodes = rowNode.SelectNodes("ss:Cell", ns);
if (cellNodes == null) continue;
int lastIdx = 0;
foreach (XmlNode cell in cellNodes)
{
string idxAttr = cell.Attributes?["ss:Index"]?.Value;
int idx = !string.IsNullOrEmpty(idxAttr) ? int.Parse(idxAttr) : lastIdx + 1;
lastIdx = idx;
}
if (lastIdx > maxCols) maxCols = lastIdx;
}
}
if (rowNodes != null)
{
foreach (XmlNode rowNode in rowNodes)
{
var cellNodes = rowNode.SelectNodes("ss:Cell", ns);
List<string> rowVals = new List<string>();
int lastIdx = 0;
if (cellNodes != null)
{
foreach (XmlNode cell in cellNodes)
{
string idxAttr = cell.Attributes?["ss:Index"]?.Value;
int idx = !string.IsNullOrEmpty(idxAttr) ? int.Parse(idxAttr) : lastIdx + 1;
// 补齐空单元格(ss:Index 跳过的列)
while (rowVals.Count < idx - 1) rowVals.Add("");
var dataNode = cell.SelectSingleNode("ss:Data", ns);
rowVals.Add(dataNode?.InnerText ?? "");
lastIdx = idx;
}
}
// 统一补齐到 maxCols,确保所有行列数一致
while (rowVals.Count < maxCols) rowVals.Add("");
rows.Add(rowVals.ToArray());
}
}
return rows;
}
/// <summary>
/// 读取二进制 .xls(OLE2 复合文档)格式,使用 Spire.XLS 引擎
/// </summary>
public static List<string[]> ReadXlsBinaryRows(byte[] fileBytes)
{
List<string[]> rows = new List<string[]>();
using (var ms = new MemoryStream(fileBytes))
using (var workbook = new Spire.Xls.Workbook())
{
workbook.LoadFromStream(ms);
if (workbook.Worksheets.Count == 0) return rows;
var sheet = workbook.Worksheets[0];
System.Data.DataTable dt = sheet.ExportDataTable();
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string[] arr = new string[dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
arr[j] = CellValueToString(dt.Rows[i][j]);
rows.Add(arr);
}
}
}
return rows;
}
/// <summary>
/// 单元格值转字符串:日期->yyyy-MM-dd,整数->不带小数,其余按实际类型
/// </summary>
private static string CellValueToString(object val)
{
if (val == null || val == System.DBNull.Value) return "";
if (val is DateTime) return ((DateTime)val).ToString("yyyy-MM-dd");
if (val is double)
{
double d = (double)val;
if (d == Math.Floor(d) && Math.Abs(d) < 1e15) return ((long)d).ToString();
return d.ToString("0.############", System.Globalization.CultureInfo.InvariantCulture);
}
if (val is int) return ((int)val).ToString();
if (val is long) return ((long)val).ToString();
if (val is decimal) return Convert.ToDecimal(val).ToString(System.Globalization.CultureInfo.InvariantCulture);
if (val is bool) return ((bool)val) ? "TRUE" : "FALSE";
return val.ToString();
}
/// <summary>
/// 读取 .xlsx(OpenXML)格式
/// 处理空单元格(列字母 r 引用定位)和共享字符串
/// </summary>
public static List<string[]> ReadXlsxRows(byte[] fileBytes)
{
List<string[]> rows = new List<string[]>();
using (var ms = new MemoryStream(fileBytes))
using (var archive = new ZipArchive(ms, ZipArchiveMode.Read))
{
// 读取共享字符串
Dictionary<int, string> shared = new Dictionary<int, string>();
var sharedEntry = archive.GetEntry("xl/sharedStrings.xml");
if (sharedEntry != null)
{
using (var s = sharedEntry.Open())
{
XmlDocument doc = new XmlDocument();
doc.Load(s);
var ns = new XmlNamespaceManager(doc.NameTable);
ns.AddNamespace("a", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
var siNodes = doc.SelectNodes("//a:si", ns);
int siIdx = 0;
if (siNodes != null)
{
foreach (XmlNode si in siNodes)
{
StringBuilder sb = new StringBuilder();
var tNodes = si.SelectNodes(".//a:t", ns);
if (tNodes != null)
foreach (XmlNode t in tNodes) sb.Append(t.InnerText);
shared[siIdx++] = sb.ToString();
}
}
}
}
// 读取第一个工作表
var sheetEntry = archive.Entries.FirstOrDefault(e => e.FullName.StartsWith("xl/worksheets/sheet"));
if (sheetEntry == null) return rows;
using (var s = sheetEntry.Open())
{
XmlDocument doc = new XmlDocument();
doc.Load(s);
var ns = new XmlNamespaceManager(doc.NameTable);
ns.AddNamespace("a", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
var rowNodes = doc.SelectNodes("//a:row", ns);
if (rowNodes != null)
{
foreach (XmlNode rowNode in rowNodes)
{
var cellNodes = rowNode.SelectNodes("a:c", ns);
// 需要按列位置排序,用字典按列字母转序号
SortedDictionary<int, string> colMap = new SortedDictionary<int, string>();
if (cellNodes != null)
{
foreach (XmlNode cell in cellNodes)
{
string refAttr = cell.Attributes?["r"]?.Value ?? "";
string type = cell.Attributes?["t"]?.Value ?? "";
int colIdx = ColRefToIndex(refAttr);
var vNode = cell.SelectSingleNode("a:v", ns);
string val = "";
if (type == "s" && vNode != null)
{
int si;
if (int.TryParse(vNode.InnerText, out si) && shared.ContainsKey(si))
val = shared[si];
}
else if (vNode != null) val = vNode.InnerText;
colMap[colIdx] = val;
}
}
int maxCol = colMap.Count > 0 ? colMap.Keys.Max() : 0;
string[] arr = new string[maxCol + 1];
foreach (var kv in colMap) arr[kv.Key] = kv.Value;
rows.Add(arr);
}
}
}
}
return rows;
}
/// <summary>
/// Excel列字母引用转0基索引,如 A1->0
/// </summary>
public static int ColRefToIndex(string refAttr)
{
if (string.IsNullOrEmpty(refAttr)) return 0;
string colPart = new string(refAttr.Where(char.IsLetter).ToArray());
int idx = 0;
foreach (char c in colPart.ToUpper())
idx = idx * 26 + (c - 'A' + 1);
return idx - 1;
}
/// <summary>
/// 将DataTable导出为Excel(HTML table格式,Excel可直接打开)
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="sheetName">工作表名</param>
/// <returns>带UTF-8 BOM的Excel文件字节</returns>
public static byte[] ExportHtmlTable(System.Data.DataTable dt, string sheetName)
{
// 生成真正的 XML Spreadsheet 2003 格式(.xls),确保 ReadXlsXmlRows 能解析
StringBuilder sb = new StringBuilder();
sb.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
sb.AppendLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sb.AppendLine("<Worksheet ss:Name=\"" + sheetName + "\">");
sb.AppendLine("<Table>");
sb.Append("<Row>");
foreach (System.Data.DataColumn c in dt.Columns)
sb.Append("<Cell><Data ss:Type=\"String\">" + c.ColumnName + "</Data></Cell>");
sb.AppendLine("</Row>");
foreach (System.Data.DataRow r in dt.Rows)
{
sb.Append("<Row>");
foreach (System.Data.DataColumn c in dt.Columns)
sb.Append("<Cell><Data ss:Type=\"String\">" + r[c.ColumnName].ToStringEx() + "</Data></Cell>");
sb.AppendLine("</Row>");
}
sb.AppendLine("</Table>");
sb.AppendLine("</Worksheet>");
sb.AppendLine("</Workbook>");
// 转UTF-8 BOM,确保Excel打开中文不乱码
return Encoding.UTF8.GetPreamble()
.Concat(Encoding.UTF8.GetBytes(sb.ToString())).ToArray();
}
/// <summary>
/// 生成Excel下载的Http响应(IHttpActionResult)
/// </summary>
/// <param name="bytes">Excel文件字节</param>
/// <param name="fileName">下载文件名</param>
public static System.Web.Http.IHttpActionResult DownloadResult(byte[] bytes, string fileName)
{
var resp = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.OK)
{
Content = new System.Net.Http.ByteArrayContent(bytes)
{
Headers =
{
ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.ms-excel"),
ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment")
{
FileName = fileName
}
}
}
};
return new System.Web.Http.Results.ResponseMessageResult(resp);
}
}
}