using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;using System.IO;
using System.Text;
using ICSharpCode.SharpZipLib.Zip;
using NPOI.SS.UserModel;using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using ICSharpCode.SharpZipLib.Core;
namespace FolyerERP
{ public class ExportBaseList : Jeez.Runtime.Base.General.frmList
{ public DataTable GetExDataTable(DataTable dt)
{ return RefreshDataTable(dt);
} protected override DataTable RefreshDataTable(DataTable dt)
{ return base.RefreshDataTable(dt);
} protected override void Export()
{
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Excel檔案(*.xlsx)|*.xlsx"; if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{ string filePath = sfd.FileName;
ExcelExport.Export(this, filePath
);
} //base.Export(); }
} public static class frmListExtend
{ private static Dictionary<string, bool> dicCol = new Dictionary<string, bool>(); public static bool GetEntityAnnosDt(this ExportBaseList list, int pIndex, out DataTable result)
{
result = new DataTable();
pIndex = (pIndex == 0 ? 1 : pIndex); int beginIdx = (pIndex == 1 ? 1 : (pIndex - 1) * 500 + 1);
Jeez.Common.UI.MainStatusBar.ProgressBarValue += 500; if (beginIdx > list.CurrentGrid.RowCount)
{
result = new DataTable(); return true;
} try
{ if (list.SQLTableName != "")
{
result = list.ReportListData.GetRptListData(beginIdx, beginIdx + 499);
} else
result = list.GetExDataTable(list.ReportListData.GetRptListData(beginIdx, beginIdx + 499)); foreach (DataColumn col in result.Columns)
{ if (!dicCol.ContainsKey(col.ColumnName))
{
dicCol.Add(col.ColumnName, false);
}
} foreach (KeyValuePair<string, bool> kv in dicCol)
{ if (result.Columns.Contains(kv.Key) && !kv.Value)
{
result.Columns.Remove(kv.Key);
}
} if (list.SQLTableName == "")
{
DataTable dt = new DataTable(); foreach (Jeez.Control.JeezGrid.Column col in list.CurrentGrid.Columns)
{ if (col.Visible)
{ if (col.EntityCol != null && col.EntityCol.Value != null)
{
dt.Columns.Add(col.Name, col.EntityCol.Value.SystemType);//增加快取列。 } else
{
dt.Columns.Add(col.Name, GetColType(col.ColDataType));
}
}
} foreach (DataRow dr in result.Rows)
{
DataRow r = dt.NewRow(); foreach (DataColumn dc in result.Columns)
{ if (dt.Columns.Contains(dc.ColumnName))
{
r[dc.ColumnName] = dr[dc.ColumnName];
}
}
dt.Rows.Add(r);
}
dt.AcceptChanges();
result = dt;
}
} catch
{
result = null; return false;
} return true;
} private static Type GetColType(Jeez.Control.JeezGrid.ColumnDataType coltype)
{ switch (coltype)
{ case Jeez.Control.JeezGrid.ColumnDataType.Boolean: return typeof(bool); case Jeez.Control.JeezGrid.ColumnDataType.Date: case Jeez.Control.JeezGrid.ColumnDataType.Time: case Jeez.Control.JeezGrid.ColumnDataType.DateTime: return typeof(DateTime); case Jeez.Control.JeezGrid.ColumnDataType.Decimal: case Jeez.Control.JeezGrid.ColumnDataType.Money: return typeof(decimal); case Jeez.Control.JeezGrid.ColumnDataType.Integer: return typeof(int); case Jeez.Control.JeezGrid.ColumnDataType.Text: return typeof(string); case Jeez.Control.JeezGrid.ColumnDataType.Byte: return typeof(byte[]); default: return typeof(string);
}
} public static List<string> GetColName(this ExportBaseList list)
{
dicCol = new Dictionary<string, bool>();
List<string> lsCols = new List<string>(); foreach (Jeez.Control.JeezGrid.Column ccol in list.CurrentGrid.Columns)
{ if (ccol.Visible)
{
lsCols.Add(ccol.Caption);
}
dicCol.Add(ccol.Name, ccol.Visible);
} return lsCols;
}
} public class ExcelExport
{ public static void Export(ExportBaseList list, string strFileName)
{
Jeez.Common.UI.MainStatusBar.ProgressBarMaxValue = list.CurrentGrid.RowCount;
Jeez.Common.UI.MainStatusBar.ProgressBarValue = 1;
Jeez.Common.UI.MainStatusBar.ProgressBarVisible = true;
Jeez.Common.UI.MessageTip tip = new Jeez.Common.UI.MessageTip("正在匯出，請稍侯..."); try
{
XlsxOutputHelper xlsxOutput = new XlsxOutputHelper(strFileName); //更新進度條
xlsxOutput.BeginGenerate(new List<string> { "sheet1" });
DataPackage QureyService(int pIndex, int flag)
{ if (!list.GetEntityAnnosDt(pIndex, out DataTable result))
{ return new DataPackage(false, null);
} return new DataPackage(true, result);
}
xlsxOutput.TotalCnt = 0; if (!xlsxOutput.GenerateSheet(list.GetColName(), "sheet1", QureyService))
{
tip.CloseMessageTip(); //匯出失敗
Jeez.Common.UI.MsgBox.Inform("匯出失敗");
} else
{
tip.CloseMessageTip();
Jeez.Common.UI.MsgBox.Inform("匯出成功");
} var isOk = xlsxOutput.EndGenerate((endmsg) =>
{
Jeez.Common.UI.MainStatusBar.StatusBarMainText = endmsg;
});
} catch (Exception ex)
{
Jeez.Common.UI.MsgBox.Inform("匯出失敗:" + ex.Message);
} finally
{
tip.CloseMessageTip();
Jeez.Common.UI.MainStatusBar.ProgressBarVisible = false;
}
}
} public class XlsxOutputHelper
{ private const int FlushCnt = 2000; private static readonly string _tempFilePath = Jeez.Common.UI.FileIO.FileOperate.GetAppTempPath(); public int TotalCnt = 0; public Action<int> ProgressShow = null; private readonly string _batchId; private List<EntryPackage> _sheetFileList = new List<EntryPackage>(); private readonly string _filePath; private readonly string _tempFile; private Dictionary<string, XSSFCellStyle> _styles; public XlsxOutputHelper(string filePath)
{ var ext = Path.GetExtension(filePath); if (ext != ".xlsx")
{
_filePath = Path.GetFileNameWithoutExtension(filePath) + ".xlsx";
} else
{
_filePath = filePath;
}
File.Create(_filePath).Close();
_batchId = Guid.NewGuid().ToString("N");
_tempFile = _tempFilePath + "/" + _batchId + ".xlsx";
} public void BeginGenerate(List<string> sheetNames)
{
XSSFWorkbook wb = new XSSFWorkbook();
_styles = CreateStyles(wb); foreach (var sheetName in sheetNames)
{
wb.CreateSheet(sheetName);
} using (var os = new FileStream(_tempFile, FileMode.Create, FileAccess.ReadWrite))
{
wb.Write(os);
}
} /// <summary>
/// 生成Excel,多個sheet檔案外部呼叫方法 /// </summary>
/// <param name="headList">標題列表</param>
/// <param name="sheetName">工作薄sheet名稱</param>
/// <param name="querySerivce">查詢服務</param>
public bool GenerateSheet(List<string> headList, string sheetName, Func<int/*頁碼*/, int/*資料標識*/, DataPackage/*返回資料*/> querySerivce)
{ if (!File.Exists(_tempFile)) throw new Exception("請先執行BeginGenerate方法");
XSSFWorkbook wb = new XSSFWorkbook(_tempFile);
XSSFSheet sheet = (XSSFSheet)wb.GetSheet(sheetName); string sheetRef = sheet.GetPackagePart().PartName.Name; string sheetRefList = sheetRef.Substring(1);
wb.Close(); if (!Directory.Exists(_tempFilePath))
{
Directory.CreateDirectory(_tempFilePath);
} string guid = Guid.NewGuid().ToString("N"); string sheetFileListFile = _tempFilePath + "/sheet_" + guid + ".xml"; bool isOk = true; using (var s = File.OpenWrite(sheetFileListFile))
{ using (StreamWriter fw = new StreamWriter(s, Encoding.UTF8))
{ //生成sheet
if (!GenerateExcelSheet(headList, fw, _styles, querySerivce))
{
isOk = false;
}
}
} if (!isOk)
{
Jeez.Common.UI.FileIO.FileOperate.DeleteFile(sheetFileListFile); return false;
}
_sheetFileList.Add(new EntryPackage() { EntryPath = sheetRefList, XmlFile = sheetFileListFile }); return true;
} /// <summary>
/// 結束生成Excel寫入檔案到本地 /// </summary>
/// <param name="writefileConsole"></param>
/// <returns></returns>
public bool EndGenerate(Action<string> writefileConsole)
{ if (!File.Exists(_tempFile)) throw new Exception("請先執行BeginGenerate方法"); if (_sheetFileList == null || _sheetFileList.Count == 0) return false;
writefileConsole("正在寫入檔案，請耐心等待...."); //將臨時檔案壓縮替換
using (var output = File.OpenWrite(_filePath))
{
SubstituteAll(_tempFile, _sheetFileList, output);
} // 刪除臨時檔案 Jeez.Common.UI.FileIO.FileOperate.DeleteFile(_tempFile); foreach (var entryPackage in _sheetFileList)
{
Jeez.Common.UI.FileIO.FileOperate.DeleteFile(entryPackage.XmlFile);
}
writefileConsole("就緒"); return true;
} /// <summary>
/// 生成sheet /// </summary>
/// <param name="headList"></param>
/// <param name="output"></param>
/// <param name="styles"></param>
/// <param name="querySerivce"></param>
private bool GenerateExcelSheet(List<string> headList, StreamWriter output,
Dictionary<string, XSSFCellStyle> styles, Func<int/*頁碼*/, int/*資料標識*/, DataPackage/*返回資料*/> querySerivce)
{
XSSFCellStyle stringStyle = styles["cell_string"];
XSSFCellStyle longStyle = styles["cell_long"];
XSSFCellStyle doubleStyle = styles["cell_double"];
XSSFCellStyle dateStyle = styles["cell_date"];
SpreadsheetWriter sw = new SpreadsheetWriter(output); int[] arrColWidth = new int[headList.Count]; for (int i = 0; i < headList.Count; i++)
{
arrColWidth[i] = Math.Max(Encoding.GetEncoding(936).GetBytes(headList[i]).Length, 10);
}
sw.BeginWorkSheet();
sw.BeginSetColWidth(); for (int i = 0; i < headList.Count; i++)
{
sw.SetColWidthBeforeSheet(i, arrColWidth[i] + 1);
}
sw.EndSetColWidth();
sw.BeginSheet(); // 表頭
sw.InsertRowWithheight(0, headList.Count, 15); int styleIndex = styles["sheet_title"].Index; for (int i = 0, len = headList.Count; i < len; i++)
{
sw.CreateCell(i, headList[i], styleIndex);
}
sw.EndWithheight(); // int pageIndex = 1;// 查詢起始頁
bool hasNextRow;// 是否還有資料，迴圈條件
int flag = 0;//用於多批資料的處理
int rownum = 1;//總行數
do
{// 開始分頁查詢 // 匯出查詢改為分頁查詢方式，替代原有queryExportResult()方法
DataPackage data = querySerivce(pageIndex, flag); if (!data.IsSucess) return false; if (flag == 0 || data.Flag == 0) flag = data.Flag; if (flag != 0 && flag != data.Flag)
{
flag = data.Flag;
pageIndex = 1;
hasNextRow = true; continue;
} var dt = data.Table; if (dt != null && dt.Rows.Count > 0)
{ int cellIndex; foreach (DataRow row in dt.Rows)
{
cellIndex = 0;
sw.InsertRow(rownum); #region 填充內容 foreach (DataColumn column in dt.Columns)
{ string drValue = row[column].ToString(); if (string.IsNullOrWhiteSpace(drValue))
{
sw.CreateCell(cellIndex, "", stringStyle.Index);
} else
{ switch (column.DataType.ToString())
{ case "System.DateTime"://日期型別
DateTime.TryParse(drValue, out DateTime dateV);
sw.CreateCell(cellIndex, dateV, dateStyle.Index); break; case "System.Int16"://整型
case "System.Int32": case "System.Int64": case "System.Byte": int.TryParse(drValue, out int intV);
sw.CreateCell(cellIndex, intV, longStyle.Index); break; case "System.Decimal"://浮點型
case "System.Double": double.TryParse(drValue, out double doubV);
sw.CreateCell(cellIndex, doubV, doubleStyle.Index); break; case "System.DBNull"://空值處理
sw.CreateCell(cellIndex, "", stringStyle.Index); break; default:
sw.CreateCell(cellIndex, drValue.Replace("<", "<").Replace(">", ">"),
stringStyle.Index); break;
}
}
cellIndex++;
} #endregion
sw.EndRow(); if (rownum % FlushCnt == 0)
{
output.Flush();
}
rownum++;
}
ProgressShow?.Invoke(TotalCnt += rownum - 1);
hasNextRow = true;
pageIndex++;
} else
{
hasNextRow = false;
}
GC.Collect();
} while (hasNextRow);
sw.EndSheet();
sw.EndWorkSheet(); return true;
} /// <summary>
/// 建立Excel樣式 /// </summary>
/// <param name="wb"></param>
/// <returns></returns>
private static Dictionary<string, XSSFCellStyle> CreateStyles(XSSFWorkbook wb)
{
Dictionary<string, XSSFCellStyle> stylesMap = new Dictionary<string, XSSFCellStyle>();
IDataFormat fmt = wb.CreateDataFormat();
ICellStyle style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
stylesMap.Add("cell_string", (XSSFCellStyle)style);
ICellStyle style2 = wb.CreateCellStyle();
style2.DataFormat = fmt.GetFormat("0");
style2.Alignment = HorizontalAlignment.Center;
style2.VerticalAlignment = VerticalAlignment.Center;
stylesMap.Add("cell_long", (XSSFCellStyle)style2);
ICellStyle style3 = wb.CreateCellStyle();
style3.DataFormat = fmt.GetFormat("0.00");
style3.Alignment = HorizontalAlignment.Center;
style3.VerticalAlignment = VerticalAlignment.Center;
stylesMap.Add("cell_double", (XSSFCellStyle)style3);
ICellStyle style4 = wb.CreateCellStyle();
style4.DataFormat = fmt.GetFormat("yyyy-MM-dd");
style4.Alignment = HorizontalAlignment.Center;
style4.VerticalAlignment = VerticalAlignment.Center;
stylesMap.Add("cell_date", (XSSFCellStyle)style4);
ICellStyle style5 = wb.CreateCellStyle();
style5.FillForegroundColor = IndexedColors.Grey25Percent.Index;
style5.FillPattern = FillPattern.SolidForeground;
style5.Alignment = HorizontalAlignment.Center;
style5.VerticalAlignment = VerticalAlignment.Center;
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
style5.SetFont(font);
stylesMap.Add("sheet_title", (XSSFCellStyle)style5); return stylesMap;
} /// <summary>
/// 打包壓縮 /// </summary>
/// <param name="zipfile"></param>
/// <param name="sheetList"></param>
/// <param name="output"></param>
private void SubstituteAll(string zipfile, List<EntryPackage> sheetList, Stream output)
{ using (ZipOutputStream zos = new ZipOutputStream(output))
{ using (ZipFile zip = new ZipFile(zipfile))
{
IEnumerator en = zip.GetEnumerator(); while (en.MoveNext())
{ if (en.Current == null) continue;
ZipEntry ze = (ZipEntry)en.Current; if (!sheetList.Exists(e => e.EntryPath.Contains(ze.Name)))
{
zos.PutNextEntry(new ZipEntry(ze.Name));
Stream tis = zip.GetInputStream(ze); var length = ze.Size; byte[] buffer = new byte[2048];
StreamUtils.Copy(tis, zos, buffer);
}
} foreach (var sheetEntry in sheetList)
{
zos.PutNextEntry(new ZipEntry(sheetEntry.EntryPath)); using (Stream lis = new FileStream(sheetEntry.XmlFile, FileMode.Open, FileAccess.ReadWrite))
{ var length = lis.Length; byte[] buffer = new byte[2048];
StreamUtils.Copy(lis, zos, buffer);
}
}
}
}
} /// <summary>
/// 打包壓縮 /// </summary>
/// <param name="zipfile"></param>
/// <param name="xmlfile"></param>
/// <param name="entryList"></param>
/// <param name="output"></param>
private void SubstituteAll(string zipfile, string xmlfile, string entryList, Stream output)
{ using (ZipOutputStream zos = new ZipOutputStream(output))
{ using (ZipFile zip = new ZipFile(zipfile))
{
IEnumerator en = zip.GetEnumerator(); while (en.MoveNext())
{ if (en.Current == null) continue;
ZipEntry ze = (ZipEntry)en.Current; if (!entryList.Contains(ze.Name))
{
zos.PutNextEntry(new ZipEntry(ze.Name));
Stream tis = zip.GetInputStream(ze); var length = ze.Size;
StreamUtils.Copy(tis, zos, null);
}
}
zos.PutNextEntry(new ZipEntry(entryList)); using (Stream lis = new FileStream(xmlfile, FileMode.Open, FileAccess.ReadWrite))
{ var length = lis.Length;
StreamUtils.Copy(lis, zos, null);
}
}
}
}
} public class SpreadsheetWriter
{ private StreamWriter _out; private int _rownum; public SpreadsheetWriter(StreamWriter output)
{ this._out = output;
} public void BeginWorkSheet()
{ this._out
.Write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"> <dimension ref=\"A1\"/>" + "<sheetViews><sheetView showRuler=\"1\" showOutlineSymbols =\"1\" defaultGridColor =\"1\" colorId =\"64\" zoomScale =\"100\" workbookViewId =\"0\" ></sheetView></sheetViews><sheetFormatPr baseColWidth=\"8\" defaultRowHeight =\"15\" />");
} public void BeginSheet()
{ this._out.Write("<sheetData>\n");
} public void EndSheet()
{ this._out.Write("</sheetData>"); // 合併單元格 } public void EndWorkSheet()
{ this._out.Write("</worksheet>");
} //插入行 不帶高度
public void InsertRow(int rownum)
{ this._out.Write("<row r=\"" + (rownum + 1) + "\">\n"); this._rownum = rownum;
} public void EndRow()
{ this._out.Write("</row>\n");
} //插入行且設定高度
public void InsertRowWithheight(int rownum, int columnNum, double height)
{ this._out.Write("<row r=\"" + (rownum + 1) + "\" spans=\"1:"
+ columnNum + "\" ht=\"" + height + "\" customHeight=\"1\">\n"); this._rownum = rownum;
} public void EndWithheight()
{ this._out.Write("</row>\n");
} public void BeginSetColWidth()
{ this._out.Write("<cols>\n");
} // 設定列寬 下標從0開始
public void SetColWidthBeforeSheet(int columnIndex, double columnWidth)
{ this._out.Write("<col min=\"" + (columnIndex + 1) + "\" max=\""
+ (columnIndex + 1) + "\" width=\"" + columnWidth + "\" customWidth=\"1\"/>\n");
} public void EndSetColWidth()
{ this._out.Write("</cols>\n");
} public void BeginMergerCell()
{ this._out.Write("<mergeCells>\n");
} public void EndMergerCell()
{ this._out.Write("</mergeCells>\n");
} // 合併單元格 下標從0開始
public void SetMergeCell(int beginColumn, int beginCell, int endColumn, int endCell)
{ this._out.Write("<mergeCell ref=\"" + GetExcelName(beginCell + 1) + (beginColumn + 1) + ":" + GetExcelName(endCell + 1) + (endColumn + 1) + "\"/>\n");// 列行:列行
} public void CreateCell(int columnIndex, string value, int styleIndex)
{ string cellref = new CellReference(this._rownum, columnIndex)
.FormatAsString(); this._out.Write("<c r=\"" + cellref + "\" t=\"inlineStr\""); if (styleIndex != -1) this._out.Write(" s=\"" + styleIndex + "\""); this._out.Write(">"); this._out.Write("<is><t>" + value + "</t></is>"); this._out.Write("</c>");
} public void CreateCell(int columnIndex, string value)
{
CreateCell(columnIndex, value, -1);
} public void CreateCell(int columnIndex, double value, int styleIndex)
{ string cellref = new CellReference(this._rownum, columnIndex)
.FormatAsString(); this._out.Write("<c r=\"" + cellref + "\" t=\"n\""); if (styleIndex != -1) this._out.Write(" s=\"" + styleIndex + "\""); this._out.Write(">"); this._out.Write("<v>" + value + "</v>"); this._out.Write("</c>");
} public void CreateCell(int columnIndex, double value)
{
CreateCell(columnIndex, value, -1);
} public void CreateCell(int columnIndex, DateTime value, int styleIndex)
{
CreateCell(columnIndex, DateUtil.GetExcelDate(value, false),
styleIndex);
} //10 進位制轉26進位制
private string GetExcelName(int i)
{ char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
List<char> sb = new List<char>(); while (i > 0)
{
sb.Add(allChar[i % 26 - 1]);
i /= 26;
}
sb.Reverse(); return string.Join("", sb);
}
} public class DataPackage
{ public bool IsSucess { get; set; } /// <summary>
/// 資料標識 /// </summary>
public int Flag { get; set; } public DataTable Table { get; set; } public DataPackage(bool isSucess) : this(isSucess, null, 0)
{
} public DataPackage(bool isSucess, DataTable table) : this(isSucess, table, 0)
{
} public DataPackage(bool isSucess, DataTable table, int flag)
{
IsSucess = isSucess;
Table = table;
Flag = flag;
}
} public class EntryPackage
{ public string EntryPath { get; set; } public string XmlFile { get; set; }
}
}
經過測試原來匯出10萬條資料要10分鐘左右，現在只要10s!
大家還有更好的方法歡迎推薦！
這裡需要引用平臺的一些dll元件，如果沒有的可以這裡下載！
低程式碼快速開發平臺下載連結：
提取碼：1234