本文介绍了一个使用OpenXml SDK 2.0操作Excel文件的实用类库ExcelHelper，并提供了详细的示例代码，展示了如何读写Excel文件，包括获取单元格值、设置单元格值等常见操作。
要使用OpenXml首先要下载最新的Open XML Format SDK 2.0。具体的导入openxml的dll，去网上搜，很多
1.我个人写的XmlHelp类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Data;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace CreateExcelSpreadsheet
{
public class ExcelHelper
{
/// <summary>
/// 获取指定单元格的值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="stringTablePart">SharedStringTablePart类型</param>
/// <returns></returns>
public static string GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
string returnStr = string.Empty;
try
{
if (cell.ChildElements.Count == 0)
return null;
returnStr = cell.CellValue.InnerText;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
returnStr = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(returnStr)].InnerText;
}
catch (Exception ex) {
//returnStr = ex.Message;
throw ex;
}
return returnStr;
}
/// <summary>
/// 获取指定的行
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowIndex">行号</param>
/// <returns></returns>
public static Row GetRow(WorkbookPart workBook,Worksheet worksheet,int rowIndex)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
/// <summary>
/// 根据行，列获取指定的单元格
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowIndex">行号</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public static Cell GetCell(WorkbookPart workBook,Worksheet worksheet, int rowIndex, string columnName)
{
Row row = GetRow(workBook,worksheet, rowIndex);
if (row == null)
return null;
return row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
/// <summary>
/// 给指定的单元格赋值
/// </summary>
/// <param name="cell"></param>
/// <param name="newValue">所要赋值的数据</param>
public static string SetValue(Cell cell, object newValue)
{
string returnStr = string.Empty;
try
{
CellValue v = new CellValue(newValue.ToString());
cell.CellValue = v;
}
catch (Exception ex) {
//returnStr = ex.Message;
throw ex;
}
return returnStr;
}
/// <summary>
/// 更新指定行，列的单元格的值
/// </summary>
/// <param name="workSheet"></param>
/// <param name="newValue">所要赋值的数据</param>
/// <param name="rowIndex">行号</param>
/// <param name="columnName">列名</param>
public static string SetValue(WorkbookPart workBook, Worksheet workSheet, object newValue, int rowIndex, string columnName)
{
string returnStr = string.Empty;
try{
Cell cell = GetCell(workBook,workSheet, rowIndex, columnName);
cell.CellValue = new CellValue(newValue.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
workSheet.Save();
}catch(Exception ex){
//returnStr = ex.Message;
throw ex;
}
return returnStr;
}
/// <summary>
/// 提起Worksheet的指定Row 作为DataTable的列名
/// </summary>
/// <param name="dt"></param>
/// <param name="workSheet"></param>
/// <param name="row"></param>
public static string GetDataTableColumn(ref DataTable dt,WorkbookPart workBook,Worksheet worksheet,Row row)
{
string returnStr = string.Empty;
try
{
DataColumn dc = new DataColumn();
// SharedStringTable sst = workbookPart.SharedStringTablePart.SharedStringTable ;
foreach (Cell cell in row)
{
string cellValue = GetValue(cell, workBook.SharedStringTablePart);
dc = new DataColumn(cellValue);
dt.Columns.Add(dc);
}
}
catch (Exception ex) {
//returnStr = ex.Message;
throw ex;
}
return returnStr;
}
/// <summary>
/// 把Worksheet中的数据一行一行的加进DataTable中
/// </summary>
/// <param name="dt"></param>
/// <param name="worksheet"></param>
/// <param name="row"></param>
public static string GetDataTableRow(ref DataTable dt,WorkbookPart workBook,Worksheet worksheet,Row row) {
string returnStr = string.Empty;
try
{
DataRow dr = dt.NewRow();
int i = 0;
foreach (Cell cell in row)
{
string cellValue = GetValue(cell, workBook.SharedStringTablePart);
dr[i++] = cellValue;
}
dt.Rows.Add(dr);
}
catch (Exception ex) {
//returnStr = ex.Message;
throw ex;
}
return returnStr;
}
/// <summary>
/// 读取Excel的Sheet页到DataTable，默认Sheet的第一行是列名
/// </summary>
/// <param name="workSheet"></param>
/// <returns></returns>
public static string GetDataTableBySheet(ref DataTable dt,WorkbookPart workBook, Worksheet workSheet)
{
string returnStr = string.Empty;
try
{
IEnumerable<Row> rows = workSheet.Descendants<Row>();
foreach (Row row in rows)
{
if (row.RowIndex == 1)
{ //默认Sheet的第一行是列名
GetDataTableColumn(ref dt, workBook, workSheet, row);
}
else
{
GetDataTableRow(ref dt, workBook, workSheet, row); //更新除列名（第一行）以外的其他数据
}
}
}
catch (Exception ex) {
//returnStr = ex.Message;
throw ex;
}
return returnStr;
}
/// <summary>
/// 更新Worksheet中与DataTable相同的列
/// </summary>
/// <param name="dt">传入的DataTable的值</param>
/// <param name="workSheet"></param>
public static string UpdateDataFromDataTable(DataTable dt, WorkbookPart workBook, Worksheet workSheet)
{
string returnStr = string.Empty;
try
{
int i = 0; //workSheet的Row
int j = 0; //dt.Rows的Row
foreach (Row row_ws in workSheet){
i++;
Cell cell_workSheet = GetCell(workBook,workSheet,i,"ID");
string cell_workSheet_value = GetValue(cell_workSheet,workBook.SharedStringTablePart) ;
foreach (DataRow row_dt in dt.Rows) {
j++;
if (cell_workSheet_value == row_dt["ID"].ToString()) {
SetValue(workBook,workSheet,row_dt["Name"],i,"Name");
}
}
}
}
catch (Exception ex) {
returnStr = ex.Message;
}
return returnStr;
}
}
}
2.具体应用例子
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 using DocumentFormat.OpenXml; 8 using DocumentFormat.OpenXml.Wordprocessing; 9 using DocumentFormat.OpenXml.Packaging; 10 using DocumentFormat.OpenXml.Spreadsheet; 11 using DocumentFormat.OpenXml.Extensions; 12 using CreateExcelSpreadsheet; 13 14 namespace ConsoleApplication3 15 { 16 class Program 17 { 18 static void Main(string[] args) 19 { 20 using (SpreadsheetDocument spreedSheet = SpreadsheetDocument.Open(@"F:\test.xlsx", true)) { 21 22 WorkbookPart workBook = spreedSheet.WorkbookPart; 23 //自动计算 24 spreedSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; 25 spreedSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; 26 27 IEnumerable<Sheet> sheetXlsx = spreedSheet.WorkbookPart.Workbook.Descendants<Sheet>().Select(c=>c); 28 if (sheetXlsx.Count() == 0) 29 { 30 Console.WriteLine("Sheet页为空"); 31 } 32 else { 33 //先去第一个Sheet做测试 34 WorksheetPart worksheetPart = workBook.WorksheetParts.FirstOrDefault(); 35 // WorksheetPart worksheetPart = (WorksheetPart)spreedSheet.WorkbookPart.GetPartById(sheetXlsx.First().Id); 36 Worksheet workWheet = worksheetPart.Worksheet; 37 ExcelHelper.SetValue(workWheet, "feng", 3, "C"); 38 } 39 } 40 } 41 42 } 43 }
以上代码简单明了，但是有个问题，就是，由于excel需要设置的属性很多，在使用OpenXml的时候，难免会后漏掉设置的属性，这样在用程序修改或者创建
excel后，第一次打开的时候，会弹出如下的消息，excel会自动补全确实的属性，目前我还没找到完美的解决方案，在随后会奉上
扫一扫
1336
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
