仅此记录一下，有些许bug，但不重要，可以自行修改。
excel的工具类。
public class ExcelUtils
{///注意:
///需要提前添加DocumentFormat.OpenXml.dll
///openXml只能处理后缀为xlsx的excel文件，xls格式的不支持
private object m_objOpt = Missing.Value;
private DocumentFormat.OpenXml.Packaging.SpreadsheetDocument m_objSpreadsheetDocument = null;
private WorkbookPart m_objWorkbookPart = null;
private WorksheetPart m_objWorksheetPart = null;
private Worksheet m_objWorksheet = null;
private SheetData m_objSheetData = null;
List NormalSheets = new List();
const int BranchCodeRow = 2;
const int BranchCodeStartCol = 3;
const int DataEndRow = 7;
#region CreateExcel Interop 2013
///
/// 创建excel,并且把dataTable导入到excel中
///
/// 保存路径
/// 数据源
/// excel中sheet的名称
public void CreateExcel(string destination, DataTable[] dataTables, string[] sheetNames = null)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
foreach (DataTable table in dataTables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet();
Columns headColumns = CrateColunms(table);
sheetPart.Worksheet.Append(headColumns);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild();
string relatiOnshipId= workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements().Count() > 0)
{
sheetId =
sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = string.Empty;
if (sheetNames != null)
{
if (sheetNames.Length >= sheetId)
{
sheetName = sheetNames[sheetId - 1].ToString();
}
}
else
{
sheetName = table.TableName ?? sheetId.ToString();
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List columns = new List();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.StyleIndex = 11;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.StyleIndex = 10;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Append(sheetData);
}
workbook.Close();
}
}
private Columns CrateColunms(DataTable table)
{
int numCols = table.Columns.Count;
var columns = new Columns();
for (int col = 0; col columns = new List();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.StyleIndex = 11;
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.Append(cell);
}
sheetData.Append(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
cell.StyleIndex = 10;
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString()); //
newRow.Append(cell);
}
sheetData.Append(newRow);
}
return sheetData;
}
#endregion
public void ReadExcelFromStream(Stream stream, bool isEditable)
{
try
{
m_objSpreadsheetDocument = SpreadsheetDocument.Open(stream, isEditable);
m_objWorkbookPart = m_objSpreadsheetDocument.WorkbookPart;
m_objWorksheetPart = m_objWorkbookPart.WorksheetParts.First();
m_objWorksheet = m_objWorksheetPart.Worksheet;
m_objSheetData = m_objWorksheet.Elements().First();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
this.DisposeRead();
throw;
}
}
///
/// 获取excel中cell的值
///
///
///
/// ().ElementAt(cellId).InnerText;
}
public Cell GetCell(string columnName, int rowIndex)
{
Row row = GetRow(Convert.ToUInt32(rowIndex));
if (row == null)
{
return null;
}
return row.Elements().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First();
}
// Given a worksheet and a row index, return the row.
public Row GetRow(uint rowIndex)
{
if (m_objWorksheet == null)
{
return null;
}
return m_objWorksheet.GetFirstChild().Elements().Where(r => r.RowIndex == rowIndex).First();
}
public List GetRows()
{
if (m_objSheetData == null)
{
return null;
}
return m_objSheetData.Elements().ToList();
}
public void CloseRead()
{
if (m_objSpreadsheetDocument != null)
{
m_objSpreadsheetDocument.Dispose();
}
}
public void DisposeRead()
{
if (m_objWorksheet != null)
{
Marshal.FinalReleaseComObject(m_objWorksheet);
}
if (m_objWorksheetPart != null)
{
Marshal.FinalReleaseComObject(m_objWorksheetPart);
}
if (m_objWorkbookPart != null)
{
Marshal.FinalReleaseComObject(m_objWorkbookPart);
}
if (m_objSpreadsheetDocument != null)
{
Marshal.FinalReleaseComObject(m_objSpreadsheetDocument);
int intGeneration = GC.GetGeneration(m_objSpreadsheetDocument);
m_objSpreadsheetDocument = null;
GC.Collect(intGeneration);
}
GC.Collect();
GC.Collect();
}
public List ReadAllSheetToList(Stream stream)
{
List tables = new List();
//MemoryStream ms = new MemoryStream();
//stream.CopyTo(ms);
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false))
{
foreach (Sheet sheet in doc.WorkbookPart.Workbook.Descendants())
{
WorksheetPart worksheet = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);
List columnsNames = new List();
if (NormalSheets.Contains(sheet.Name.Value))
{
DataTable table = new DataTable(sheet.Name.Value);
foreach (Row row in worksheet.Worksheet.Descendants())
{
if (row.RowIndex == BranchCodeRow)
{
List colvalue = new List();
int colindex = 1;
int tablecolindex = 1;
table.Columns.Add("C" + tablecolindex.ToString());
foreach (Cell cell in row)
{
tablecolindex++;
if (colindex >= BranchCodeStartCol && !String.IsNullOrEmpty(GetCellValue(doc, cell).ToString().Trim()))
{
table.Columns.Add("C" + tablecolindex.ToString());
colvalue.Add(GetCellValue(doc, cell).ToString().Trim());
}
colindex++;
}
DataRow dr1 = table.NewRow();
dr1[0] = "";
for (int i1 = 0; i1 BranchCodeRow && row.RowIndex <= DataEndRow)
{
DataRow dr = table.NewRow();
int colindex = 1;
int readindex = 0;
foreach (Cell cell in row)
{
if (colindex >= BranchCodeStartCol - 1 && readindex = table.Columns.Count)
{
break;
}
colindex++;
}
table.Rows.Add(dr);
}
else if (row.RowIndex > DataEndRow)
{
break;
}
}
tables.Add(table);
}
else if (sheet.Name.Value == "Base")
{
DataTable table = new DataTable(sheet.Name.Value);
table.Columns.Add("c1");
foreach (Row row in worksheet.Worksheet.Descendants())
{
int colindex = 1;
if (row.RowIndex == 2 || row.RowIndex == 3)
{
foreach (Cell cell in row)
{
if (colindex == 2)
{
DataRow dr1 = table.NewRow();
dr1[0] = GetCellValue(doc, cell).ToString().Trim();
table.Rows.Add(dr1);
break;
}
colindex++;
}
if (row.RowIndex == 3)
{
break;
}
}
}
tables.Add(table);
}
else if (sheet.Name.Value == "MO")
{
DataTable table = new DataTable(sheet.Name.Value);
foreach (Row row in worksheet.Worksheet.Descendants())
{
if (row.RowIndex == 2)
{
List colvalue = new List();
int colindex = 1;
int tablecolindex = 1;
table.Columns.Add("C" + tablecolindex.ToString());
foreach (Cell cell in row)
{
if (colindex >= 3 && !String.IsNullOrEmpty(GetCellValue(doc, cell).ToString().Trim()))
{
tablecolindex++;
table.Columns.Add("C" + tablecolindex.ToString());
colvalue.Add(GetCellValue(doc, cell).ToString().Trim());
}
colindex++;
}
DataRow dr1 = table.NewRow();
dr1[0] = "";
for (int i1 = 0; i1 = 3 && row.RowIndex <= 7) || (row.RowIndex >= 11 && row.RowIndex <= 15) || (row.RowIndex >= 19 && row.RowIndex <= 23) || (row.RowIndex >= 27 && row.RowIndex <= 31))
{
DataRow dr = table.NewRow();
int colindex = 1;
int readindex = 0;
foreach (Cell cell in row)
{
if (colindex >= 2 && readindex table.Columns.Count)
{
break;
}
colindex++;
}
table.Rows.Add(dr);
}
else if (row.RowIndex > 31)
{
break;
}
}
tables.Add(table);
}
else
{
break;
}
}
}
return tables;
}
///
/// 将excel中第一个sheet的数据导入到DataTable
///
///
/// sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild().Elements();
//string relatiOnshipId= sheets.First().Id.Value = sheets.First(x => x.Name == "Sheet1").Id.Value;
string relatiOnshipId= sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild();
Row[] rows = sheetData.Descendants().ToArray();
// Add columns
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add((string)GetCellValue(spreadSheetDocument, cell));
}
// Add rows
for (int rowIndex = 1; rowIndex ().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, rows[rowIndex].Descendants| ().ElementAt(i));
}
dt.Rows.Add(tempRow);
}
// Release document
if (spreadSheetDocument != null)
{
int intGeneration = GC.GetGeneration(spreadSheetDocument);
spreadSheetDocument.Dispose();
spreadSheetDocument = null;
GC.Collect(intGeneration);
}
return dt;
}
private string GetCellValue(SpreadsheetDocument document, Cell cell)
{
if (cell == null)
{
return string.Empty;
}
if (cell.CellValue == null)
{
return string.Empty;
}
return cell.CellValue.InnerXml;
}
} | |
主窗口：
public MainWindow()
{
InitializeComponent();
}
private void btn_creatExcel(object sender, RoutedEventArgs e)
{
ExcelUtils excelUtils = new ExcelUtils();
excelUtils.CreateExcel(GetFileString(), new DataTable[] { CreatTable() }, new string[] { "caocao" });
}
public string GetFileString()
{
string filePath = "";
System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog();
saveFileDialog.Filter = "Excel file (*.xlsx)|*.xlsx";
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filePath = saveFileDialog.FileName;
}
return filePath;
}
public string GetOpenFileString()
{
string filePath = "";
System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog();
openFileDialog.Filter = "Excel file (*.xlsx)|*.xlsx";
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filePath = openFileDialog.FileName;
}
return filePath;
}
private DataTable CreatTable()
{
//创建DataSet对象
DataSet ds = new DataSet();
//创建DataTable对象
DataTable dt = new DataTable();
//创建列
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("学号", typeof(int));
//创建行
DataRow row = dt.NewRow();
//添加数据
row[0] = "ACB";
row[1] = 123;
//将行添加到数据表的行集合中
dt.Rows.Add(row);
return dt;
}
private void btn_readExcel(object sender, RoutedEventArgs e)
{
DataTable dt = new DataTable();
string filePath = GetOpenFileString();
ExcelUtils excelUtils = new ExcelUtils();
dt= excelUtils.ReadFirstSheetData(new System.IO.FileStream(filePath, System.IO.FileMode.Open));
this.dataGrid.ItemsSource = dt.DefaultView;
}
private void btn_exportExcel(object sender, RoutedEventArgs e)
{
string filePath = GetFileString();
ExcelUtils excelUtils = new ExcelUtils();
DataTable dt = new DataTable();
excelUtils.CreateExcel(filePath, new DataTable[] { (this.dataGrid.ItemsSource as System.Data.DataView).Table }, new string[] { "abc" });
}
}
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有