|
C# Net 使用 openxml 寫入 對象 到 Excel 中 C# Net 使用openxml寫入對象到Excel中
------------------------------------------------------------ -------------------------文尾看效果--------------------- ----------效果一（模板文件寫入集合對象）------ ----------效果二（新建文件寫入集合對象）------ ------------------------------------------------------------- -------------------------------------------------------------
加入包：OpenXml 創(chuàng)建文件：ExcelWrite.cs 復制下面全部代碼到文件 ExcelWrite.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.Reflection;
using System.ComponentModel;
namespace YGNT.Office.ExcelXml
{
/// <summary>
/// 寫入Excel
/// </summary>
public class ExcelWrite
{
/// <summary>
/// 寫入文本
/// </summary>
/// <param name="path">文件</param>
/// <param name="objs">List<T>對象，他的默認值為第一行</param>
/// <param name="sheetName">把數(shù)據(jù)加入到工作薄的工作薄名</param>
/// <param name="goRow">開始行（從1開始）</param>
/// <param name="goCol">開始列（從1開始）</param>
public static void WriteObj<T>(string path, List<T> objs, string sheetName = "", uint goRow = 1, int goCol = 1) where T : new()
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
{
var type = objs.GetType();
var enumer = type.GetInterface("IEnumerable", false);
if (type.IsGenericType && enumer != null)
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
//獲取第一個工作表
Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet);
//如果SharedStringTablePart不存在創(chuàng)建一個新的
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
else
shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
//如果部分不包含SharedStringTable，則創(chuàng)建一個。
if (shareStringPart.SharedStringTable == null)
shareStringPart.SharedStringTable = new SharedStringTable();
uint row = goRow;
int col = goCol;
List<string> paiChu = new List<string>();
T t = new T();
//表頭
//取類上的自定義特性
bool isPaiChuClass = false;
var newType = t.GetType();
var exc = (IEnumerable<ExcelColumnAttribute>)newType.GetCustomAttributes(typeof(ExcelColumnAttribute));
if (exc.Any() && !exc.First().IsShow)
isPaiChuClass = true;
//取屬性上的自定義特性
foreach (var property in newType.GetRuntimeProperties())
{
ExcelColumnAttribute att = null;
var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute));
if (atts.Any())
att = atts.First();
if (att != null && !att.IsShow)
{
paiChu.Add(property.Name);
continue;
}
//排除了類的列后不允許添加默認行
else if (isPaiChuClass)
continue;
else if (att == null || string.IsNullOrEmpty(att.ColumnName))
NewMethod(row, col, property.Name, shareStringPart, worksheetPart);
else
NewMethod(row, col, att.ColumnName, shareStringPart, worksheetPart);
col++;
}
if (!isPaiChuClass)
row++;
////表頭
//foreach (object obj in objs as dynamic)
//{
// //取類上的自定義特性
// bool isPaiChuClass = false;
// var newType = obj.GetType();
// var exc = (IEnumerable<ExcelColumnAttribute>)newType.GetCustomAttributes(typeof(ExcelColumnAttribute));
// if (exc.Any() && !exc.First().IsShow)
// isPaiChuClass = true;
// //取屬性上的自定義特性
// foreach (var property in newType.GetRuntimeProperties())
// {
// ExcelColumnAttribute att = null;
// var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute));
// if (atts.Any())
// att = atts.First();
// if (att != null && !att.IsShow)
// {
// paiChu.Add(property.Name);
// continue;
// }
// //排除了類的列后不允許添加默認行
// else if (isPaiChuClass)
// continue;
// else if (att == null || string.IsNullOrEmpty(att.ColumnName))
// NewMethod(row, col, property.Name, shareStringPart, worksheetPart);
// else
// NewMethod(row, col, att.ColumnName, shareStringPart, worksheetPart);
// col++;
// }
// if (!isPaiChuClass)
// row++;
// break;
//}
//正文
foreach (object obj in objs as dynamic)
{
col = goCol;
foreach (var property in obj.GetType().GetRuntimeProperties())
{
if (paiChu.Contains(property.Name))
continue;
//var aaa = property.PropertyType.Name;
var value = property.GetValue(obj)?.ToString() ?? "";
NewMethod(row, col, value, shareStringPart, worksheetPart);
col++;
}
row++;
}
//保存新工作表
worksheetPart.Worksheet.Save();
}
else
{
throw new Exception("需要是一個泛型集合");
}
}
}
private static void NewMethod(uint row, int column, string text, SharedStringTablePart shareStringPart, WorksheetPart worksheetPart)
{
#region 將文本插入到SharedStringTablePart中
int index = 0;
//遍歷SharedStringTable中的所有項。如果文本已經(jīng)存在，則返回其索引。
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
break;
index++;
}
//這部分沒有正文。創(chuàng)建SharedStringItem并返回它的索引。
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
#endregion
#region 將單元格A1插入工作表
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string columnName = ExcelAlphabet.ColumnToABC(column);
uint rowIndex = row;
string cellReference = columnName + rowIndex;
//如果工作表不包含具有指定行索引的行，則插入一行
Row rowobj;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
rowobj = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
rowobj = new Row() { RowIndex = rowIndex };
sheetData.Append(rowobj);
}
Cell newCell2;
//如果沒有具有指定列名的單元格，則插入一個。
if (rowobj.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
newCell2 = rowobj.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
//細胞必須按照細胞參考的順序排列。確定在何處插入新單元格。
Cell refCell = null;
foreach (Cell item in rowobj.Elements<Cell>())
{
if (item.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(item.CellReference.Value, cellReference, true) > 0)
{
refCell = item;
break;
}
}
}
Cell newCell = new Cell() { CellReference = cellReference };
rowobj.InsertBefore(newCell, refCell);
newCell2 = newCell;
}
#endregion
//設置單元格A1的值
newCell2.CellValue = new CellValue(index.ToString());
newCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
/// <summary>
/// 寫入文本
/// </summary>
/// <param name="path"></param>
/// <param name="row">行</param>
/// <param name="column">列</param>
/// <param name="text">文本</param>
/// <param name="sheetName">工作薄</param>
public static void WriteText(string path, uint row, int column, string text, string sheetName = "")
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
//如果SharedStringTablePart不存在創(chuàng)建一個新的
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
else
shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
//將文本插入到SharedStringTablePart中
int index = InsertSharedStringItem(text, shareStringPart);
//獲取第一個工作表
Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet);
//將單元格A1插入新工作表
Cell cell = InsertCellInWorksheet(ExcelAlphabet.ColumnToABC(column), row, worksheetPart);
//設置單元格A1的值
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
//保存新工作表
worksheetPart.Worksheet.Save();
}
}
//驗證指定的文本是否存在于 SharedStringTablePart 對象中，并在不存在時添加文本
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
//如果部分不包含SharedStringTable，則創(chuàng)建一個。
if (shareStringPart.SharedStringTable == null)
shareStringPart.SharedStringTable = new SharedStringTable();
int i = 0;
//遍歷SharedStringTable中的所有項。如果文本已經(jīng)存在，則返回其索引。
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
return i;
i++;
}
//這部分沒有正文。創(chuàng)建SharedStringItem并返回它的索引。
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
/// <summary>
/// 插入一個新的工作表（如Sheet2）
/// </summary>
/// <param name="workbookPart">工作簿</param>
/// <returns></returns>
public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
//向工作簿添加新工作表部件。
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
//為新工作表獲取唯一的ID
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
//附加新工作表并將其與工作簿關聯(lián)。
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
// 將新的 Cell 對象插入到 Worksheet 對象中
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
//如果工作表不包含具有指定行索引的行，則插入一行
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
//如果沒有具有指定列名的單元格，則插入一個。
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
//細胞必須按照細胞參考的順序排列。確定在何處插入新單元格。
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (cell.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
}
}
創(chuàng)建文件：ExcelSeek.cs 復制下面全部代碼到文件 ExcelSeek.cs
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace YCBX.Office.ExcelXml
{
public class ExcelSeek
{
/// <summary>
/// 在工作薄中查找工作表
/// </summary>
public static Sheet SeekSheet(WorkbookPart workbookPart, string sheetName = "")
{
//獲取所有工作薄
IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
Sheet sheet = null;
if (!sheets.Any())
throw new ArgumentException("空的Excel文檔");
if (string.IsNullOrEmpty(sheetName))
sheet = sheets.First();
else
{
if (sheets.Count(o => o.Name == sheetName) <= 0)
throw new ArgumentException($"沒有找到工作薄“{sheetName}”");
sheet = sheets.First(o => o.Name == sheetName);
}
return sheet;
}
/// <summary>
/// 根據(jù)工作表獲取工作頁
/// </summary>
/// <param name="sheet">工作表</param>
/// <returns>工作頁</returns>
public static WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, Sheet sheet)
{
return (WorksheetPart)workbookPart.GetPartById(sheet.Id);
}
}
}
創(chuàng)建文件：ExcelCreate.cs 復制下面全部代碼到文件 ExcelCreate.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace YCBX.Office.ExcelXml
{
/// <summary>
/// 創(chuàng)建Excel
/// </summary>
public class ExcelCreate
{
/// <summary>
/// 新的空白Excel文檔
/// </summary>
/// <returns></returns>
public static void NewCreate(string path)
{
//創(chuàng)建 xlsx
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook);
//將工作簿部件添加到文檔中
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//將工作表部分添加到工作簿部分
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
//將工作表添加到工作簿
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
//附加新工作表并將其與工作簿關聯(lián)
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
/// <summary>
/// 新的空白Excel文檔
/// </summary>
/// <returns>臨時的文件</returns>
public static string NewCreate()
{
var file = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx");
NewCreate(file);
//var memoryStream = new MemoryStream(File.ReadAllBytes(tempFileName));
return file;
}
}
}
創(chuàng)建文件：ExcelColumnAttribute.cs 復制下面全部代碼到文件 ExcelColumnAttribute.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
namespace YCBX.Office.ExcelXml
{
/// <summary>
/// Excel列特性
/// </summary>
public class ExcelColumnAttribute : Attribute
//: DescriptionAttribute
{
/// <summary>
/// 建議列名
/// </summary>
public virtual string ColumnName { get; }
/// <summary>
/// 是否顯示列
/// </summary>
public virtual bool IsShow { get; }
/// <summary>
/// 初始化Excel列名的特性
/// </summary>
/// <param name="isShow">是否顯示列（在類上為false時不解析默認第一行，在屬性上為false時不顯示屬性的值）</param>
public ExcelColumnAttribute(bool isShow = true)
{
IsShow = isShow;
}
/// <summary>
/// 初始化Excel列名的特性
/// </summary>
/// <param name="description">建議列名（在屬性上為Excel中的第一行的頭值）</param>
/// <param name="isShow">是否顯示列（在類上為false時不解析默認第一行，在屬性上為false時不顯示屬性的值）</param>
public ExcelColumnAttribute(string description, bool isShow = true)
{
ColumnName = description;
IsShow = isShow;
}
}
}
創(chuàng)建文件：ExcelAlphabet.cs 復制下面全部代碼到文件 ExcelAlphabet.cs
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Text;
namespace YCBX.Office.ExcelXml
{
/// <summary>
/// Excel字母碼幫助（26進制轉換）
/// </summary>
public class ExcelAlphabet
{
//備注 A 對應char為65，Z 對應char為90
/// <summary>
/// 26個字母
/// </summary>
public static uint AlphabetCount = 26;
/// <summary>
/// 數(shù)字轉字符
/// </summary>
/// <param name="iNumber"></param>
/// <returns></returns>
public static string ColumnToABC(int iNumber)
{
if (iNumber < 1 || iNumber > 702)
throw new Exception("轉為26進制可用10進制范圍為1-702");
string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int iUnits = 26;
int iDivisor = (int)(iNumber / iUnits);
int iResidue = iNumber % iUnits;
if (iDivisor == 1 && iResidue == 0)
{
iDivisor = 0;
iResidue = iResidue + iUnits;
}
else
{
if (iResidue == 0)
{
iDivisor -= 1;
iResidue += iUnits;
}
}
if (iDivisor == 0)
{
return sLetters.Substring(iResidue - 1, 1);
}
else
{
return sLetters.Substring(iDivisor - 1, 1) + sLetters.Substring(iResidue - 1, 1);
}
}
/// <summary>
/// 字符轉數(shù)字
/// </summary>
/// <param name="sString"></param>
/// <returns></returns>
public static int ABCToColumn(string sString)
{
if (string.Compare(sString, "A") == -1 || string.Compare(sString, "ZZ") == 1)
return 0;
string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int iUnits = 26;
int sFirst = -1;
int sSecond = 0;
if (sString.Length == 1)
{
sSecond = sLetters.IndexOf(sString);
}
else
{
sFirst = sLetters.IndexOf(sString.Substring(0, 1));
sSecond = sLetters.IndexOf(sString.Substring(1, 1));
}
return (sFirst + 1) * iUnits + (sSecond + 1);
}
}
}
-------------------------------------------------------------------------------------------- -------------調(diào)用方式一（模板文件寫入集合對象）---------------------------- -------------------------------------------------------------------------------------------- 1. 準備模板文件
2.準備集合model [ExcelColumn(false)]
public class StatisticalLearningModel
{
/// <summary>
/// 機構
/// </summary>
[ExcelColumn(false)]
public string Organization { get; set; }
/// <summary>
/// 班級編號
/// </summary>
[ExcelColumn(false)]
public string ClassId { get; set; }
/// <summary>
/// 班級
/// </summary>
public string Class { get; set; }
/// <summary>
/// 用戶id
/// </summary>
[ExcelColumn(false)]
public string StuId { get; set; }
/// <summary>
/// 姓名
/// </summary>
public string StuName { get; set; }
3.調(diào)用 List<StatisticalLearningModel> data = StudentDB.StatisticalLearning(dto).PageData;
//寫入到excel
var path = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx");
System.IO.File.Copy(@"OfficeFile\學員學習統(tǒng)計模板.xlsx", path, true);
ExcelWrite.WriteObj(path, data, string.Empty, 3);
4.效果
-------------------------------------------------------------------------------------------- -------------調(diào)用方式二（新建文件寫入集合對象）---------------------------- -------------------------------------------------------------------------------------------- 1.準備集合model public class StudentListModel
{
/// <summary>
/// 機構
/// </summary>
[ExcelColumn(false)]
public string Organization { get; set; }
/// <summary>
/// 班級
/// </summary>
[ExcelColumn("班級名")]
public string Class { get; set; }
/// <summary>
/// 用戶id
/// </summary>
[ExcelColumn(false)]
public string StuId { get; set; }
/// <summary>
/// 姓名
/// </summary>
[ExcelColumn("姓名")]
public string StuName { get; set; }
2.調(diào)用 var data = StudentDB.StudentList(studentList).PageData;
//寫入到excel
var path = ExcelCreate.NewCreate();
ExcelWrite.WriteObj(path, data);
3.效果
|
|
|
