使用OpenXml操作Excel，实现导入DataTabel数据<一>
最近因公司需要一直在研究OpenXml，今天难得留出时间总结一下，以备后用。下面重点讲述OpenXml在Excel中的应用，主要讲的是将Xml文件中的数据转换为DataTable，然后将DataTable的数据，导入Excel中，生成Excel电子表格。
先来了解一下OpenXml：
OpenXML（OOXML）是微软在Office 2007中提出的一种新的文档格式，Office 2007中的Word、Excel、PowerPoint默认均采用OpenXML格式 。
参考资料：可以通过下面网址去学习https://msdn.microsoft.com/zh-cn/ZH-Ch/libraty/office/cc850837.aspx
一>在介绍如何做之前，先做好准备工作：
1.下载OpenXMLSDKv2.msi，(下载地址：http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124)安装一下OpenXMLSDKv2.msi
2.在项目中引用 DocumentFormat.OpenXml.dll 和WindowsBase.dll
二>计划实现步骤：
第一步，创建Excel电子表格；
第二步，现将Xml中的数据封装到一个DataTable中；
第三步，在Excel中去DataTable的列标题作为起始行数据,从第二行起显示对应列标题的Value；
第四步，将图片插入Excel中，合并单元格，设置单元格的样式。
三>代码块实现
1.创建Excel电子表格，首先添加命名空间。
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
//创建电子表格
public sealed class XmlCreatExcel_en_US: ImportReportFromXml_en_US
{
public static WorksheetPart CurrentWorksheetPart { get; set; }
//这里先自定义一个xmldataPath和xmldataPath
string xmldataPath = @"D:\Report\20180128_150438.xml";
string excelFilePath= @"C:\Users\admin\Desktop\导出文件\myCellEx01.xlsx";
public static void CreatExcel(string xmldataPath, string excelFilePath)
{
List<string> FilePath = new List<string>();
using (var workbook = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook))
{
// create the workbook
WorkbookPart workbookpart = workbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
uint sheetId = 1;
//将Xml中的字符串数组封装到DataTable中
//设置列标题
var lists = new List<string[]>();
DataTable tblDatas = new DataTable();
DataTable[] dt = new DataTable[1] { tblDatas };
tblDatas.Columns.Add("No", typeof(Int32));
tblDatas.Columns[0].AutoIncrementSeed = 1;
tblDatas.Columns[0].AutoIncrementStep = 1;
tblDatas.Columns.Add("Dut Image", typeof(string));
tblDatas.Columns.Add("CImage", typeof(string));
tblDatas.Columns.Add("BrazedRatio", typeof(string));
DataRow dr;
for (int i = 0; i < dt.Length; i++)
{
ImportReportFromXml ReportXml = new ImportReportFromXml();
var list = ReportXml.GetExcelBottomData(xmldataPath);
lists.AddRange(list);
}
for (int k = 0; k < lists.Count; k++)//外循环:控制行数
{
dr = tblDatas.NewRow();
foreach (var RowArray in lists)//遍历每行每列的内容
{
dr["No"] = lists[k][0];
dr["Dut Image"] = lists[k][1];
dr["CImage"] = lists[k][2];
dr["BrazedRatio"] = lists[k][3];
}
tblDatas.Rows.Add(dr);
}
//将DataTable中的数据插入到Excel中
foreach (DataTable table in dt)
{
CurrentWorksheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
CurrentWorksheetPart.Worksheet = new Worksheet();//创建工作表
//添加SheetStyle，下面会写出来
WorkbookStylesPart stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
var sheetData = new SheetData();//用来存储数据的位置和内容
//设置Excel的列宽
Columns columns01 = new Columns();
columns01 = CrateColunms(table);
CurrentWorksheetPart.Worksheet.Append(columns01);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(CurrentWorksheetPart);
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet01";
//创建工作薄,并设置其属性之间的关系
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
//这句用于将数据填充到Worksheetpart中，非常重要，不可随意挪动位置。
CurrentWorksheetPart.Worksheet.Append(sheetData);
//正式插入DataTable中的数据到Excel中
//creat HeaderRow
Row headerRow = new Row() { RowIndex = 13 };//设置为第13行为首行(可行),默认RowIndex=1
//创建列
List<String> columns = new List<string>();
foreach (DataColumn column in table.Columns) //外遍历：控制列标题
{
string ColumnName = GetColumnName(columns.Count);
string rowRef = ColumnName + headerRow.RowIndex;
//增加列标题
columns.Add(column.ColumnName);
//创建单元格
Cell cell = new Cell() { CellReference = rowRef, StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue(column.ColumnName) };
headerRow.Append(cell);
}
sheetData.Append(headerRow); //添加到首行
//creat Columns
UInt32Value startRIndex = 14;
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row() { RowIndex = startRIndex, Height = 207.00, CustomHeight = true };
startRIndex = startRIndex + 1;
foreach (string col in columns)
{
switch (col)
{
case "No":
Cell cell13 = new Cell() { StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue(dsrow[col].ToString()) };
newRow.Append(cell13);
break;
case "Dut Image":
Cell cell14 = new Cell() { StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue("") };
newRow.Append(cell14);
UInt32Value a = Convert.ToUInt32(newRow.InnerText);
InsertImage(105, 382 + (a - 1) * 276, dsrow[col].ToString());//图片插入取的值是像素
break;
case "CImage":
Cell cell15 = new Cell() { StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue("") };
newRow.Append(cell15);
UInt32Value b = Convert.ToUInt32(newRow.InnerText);
InsertImage(393, 382 + (b - 1) * 276, dsrow[col].ToString());
break;
case "BrazedRatio":
Cell cell16 = new Cell() { StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue(dsrow[col].ToString()) };
newRow.Append(cell16);
break;
default:
return;
}
}
sheetData.Append(newRow);
}
// Close the document.
workbook.Close();
// 合并单元格，下面会写出来
int index = 13;
for (int i = 1; i < index; i++)
{
string cellName01 = "A" + i;
string cellName02 = "D" + i;
MergeTwoCells(excelFilePath, sheetName, cellName01, cellName02);
}
}
//创建列标题的宽度
private static Columns CrateColunms(DataTable table)
{
int numCols = table.Columns.Count;
double[] widths = { 8.38, 26.50, 30.63, 11.80 };
Columns columns = new Columns();
Column column = null;
for (UInt32Value index = 1; index <= numCols; index++)
{
column = new Column() { Min = index, Max = index, Width = widths[index - 1], CustomWidth = true };
columns.Append(column);
}
return columns;
}
}
}
