https://openxmlexporttoexcel.codeplex.com/
http://referencesource.microsoft.com/
引用:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DocumentFormat.OpenXml;//2.0 OpenXml SDK 2.0 2.5 2.6
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO.Packaging;
using System.IO;
using System.Xml;
using ClosedXML.Excel; //http://closedxml.codeplex.com/ 3.5 and 4.0 .net framework
string filename = "20160816000.xlsx";//220160815 //Book1 涂聚文测试注:这两个文件的工作内容一样 2016081600011.xlsx 此文件21KB 20160816000.xlsx 此文件容量19.1M 不规则并有隐藏列,出现读取列问题,只能用System.Data.OleDb.OleDbConnection
string sheename = "";
/// <summary>
///
/// </summary>
public OpenXmlSdkForm()
{
InitializeComponent();
}
/// <summary>
/// http://closedxml.codeplex.com/
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
public void ExportDataToExcel(DataTable dt, string fileName)
{
using (XLWorkbook wb = new XLWorkbook())
{ var ws = wb.Worksheets.Add(dt, "ws"); // worksheets name must be added.
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true; //set header style
ws.Rows(1, 1).Style.Fill.BackgroundColor = XLColor.White;
ws.Rows(1, 1).Style.Font.Bold = true;
ws.Rows(1, 1).Style.Font.FontColor = XLColor.Onyx;
ws.Columns().Width = 20; //remove AutoFilter
ws.Tables.FirstOrDefault().ShowAutoFilter = false;
// winform 用法
wb.SaveAs(filename);
//webform 用法
//Response.Clear();
//Response.Buffer = true;
//Response.Charset = "utf-8";
//Response.ContentType = "application/vnd.ms-excel";
//Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx"); //using (MemoryStream MyMemoryStream = new MemoryStream())
//{
// wb.SaveAs(MyMemoryStream);
// MyMemoryStream.WriteTo(Response.OutputStream);
// Response.Flush();
// Response.End();
//}
}
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
public void ImportExcel(string filePath,string sheetnaem)
{
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(sheetnaem);//sheetid //Create a new DataTable.
DataTable dt = new DataTable(); //Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
} dataGridView1.DataSource = dt; }
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void OpenXmlSdkForm_Load(object sender, EventArgs e)
{
getCombox(filename, comboBox1); ImportExcel(filename,this.comboBox1.SelectedValue.ToString()); }
/// <summary>
/// 涂聚文//hide column C
/// s.SetColumnHidden(2,true); https://msdn.microsoft.com/en-us/library/office/ff956189(v=office.14).aspx
/// //hide IRow 2
/// r2.ZeroHeight = true;
/// 否隐藏（isColumnHidden）
/// 20150820
/// 七夕节 涂聚文注: 隐瞒列读不出来
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">工作表名</param>
/// <returns></returns>
public static DataTable ReadIdDataTable(string fileName, string sheetName)
{ DataTable dataTable = new DataTable();
try
{
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; // spreadSheetDocument.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
// spreadSheetDocument.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; //spreadSheetDocument.WorkbookPart.Workbook.Sheets;
Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault(); //IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();//第一个工作表
//string relationshipId = theSheet.FirstOrDefault().ExtendedAttributes.ElementAt(0); //工作表
// numID = sheets.Count();
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(theSheet.Id);//第一个工作表 Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
//IEnumerable<Row> rows = sheetData.Descendants<Row>();
IEnumerable<Row> rows = sheetData.Descendants<Row>(); // //
// var list = sheetData.Descendants<Row>().Where((r) => r.Hidden != null && r.Hidden.Value).Select(r => r.RowIndex.Value).ToList<uint>(); //
//foreach (Cell cell in rows.ElementAt(0))
//{
// dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell)); //标题
//}
for (int j = 0; j < 59; j++)
{
dataTable.Columns.Add(j.ToString(), typeof(string));
}
foreach (Row row in rows)
{
DataRow dataRow = dataTable.NewRow();
//隐藏的列未显示
//for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
//{
// dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
//}
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{ //if (row.Hidden == true)
//{
// dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
//}
//else
//{ dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i)); //}
} //row.getZeroHeight() dataTable.Rows.Add(dataRow);
} }
dataTable.Rows.RemoveAt(0);
}
catch (Exception ex)
{
ex.Message.ToString();
}
return dataTable;
}
/// <summary>
///
/// </summary>
/// <param name="FileName"></param>
/// <param name="columnNumber"></param>
public void HideColumn(string FileName, UInt32Value columnNumber)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(
FileName, true))
{
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1");
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;
Columns columns1 = GenerateColumns(columnNumber);
// The column element is behind the SheetFormatProperties element.
worksheet.InsertAfter(columns1, worksheet.SheetFormatProperties);
worksheet.Save();
}
}
// Creates an Columns instance and adds its children.
public Columns GenerateColumns(UInt32Value ColumnIndex)
{
Columns columns1 = new Columns();
Column column1 = new Column() { Min = ColumnIndex, Max = ColumnIndex, Width = 0D, Hidden = true, CustomWidth = true };
columns1.Append(column1);
return columns1;
}
/// <summary>
/// Reads the specified file save path.
/// </summary>
/// <param name="fileSavePath">The file save path.</param>
/// <returns></returns>
public DataSet Read(string fileSavePath)
{ DataSet resultSet = new DataSet(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileSavePath, false))
{ WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>(); foreach (Sheet sheet in sheets)
{
DataTable dt = new DataTable(); string relationshipId = sheet.Id.Value;
string sheetName = sheet.SheetId;
dt.TableName = sheet.SheetId; WorksheetPart worksheetPart =
(WorksheetPart) spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>(); foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
} List<Row> rowsList = new List<Row>(); rowsList = rows.ToList(); //Start from 1, first row is header.
for ( int iterRowIndex = 1 ; iterRowIndex < rowsList.Count ; iterRowIndex ++) //this will also include your header row…
{
Row row = rowsList[iterRowIndex]; DataRow tempRow = dt.NewRow(); for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
} dt.Rows.Add(tempRow);
} resultSet.Tables.Add(dt);
} } return resultSet;
} /// <summary>
///
/// </summary>
/// <param name="document"></param>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
//try
//{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
string value = cell.CellValue.InnerXml; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
///
//if (cell.ChildElements.Count == 0)
// return null;
//get cell value
//String value = cell.CellValue.InnerText;
////Look up real value from shared string table
//if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
// value = stringTablePart.SharedStringTable
// .ChildElements[Int32.Parse(value)]
// .InnerText;
//return value; //}
//catch (Exception ex)
//{
// ex.Message.ToString();
// return "";
//}
}
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
static void read2(string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet; var cells = sheet.Descendants<Cell>();
var rows = sheet.Descendants<Row>(); MessageBox.Show(string.Format("Row count = {0}", rows.Count())); //LongCount
MessageBox.Show(string.Format("Cell count = {0}", cells.Count()));//LongCount // One way: go through each cell in the sheet
foreach (Cell cell in cells)
{
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
int ssid = int.Parse(cell.CellValue.Text);
string str = sst.ChildElements[ssid].InnerText;
string.Format("Shared string {0}: {1}", ssid, str);
}
else if (cell.CellValue != null)
{
string.Format("Cell contents: {0}", cell.CellValue.Text);
}
} // Or... via each row
foreach (Row row in rows)
{
foreach (Cell c in row.Elements<Cell>())
{
if ((c.DataType != null) && (c.DataType == CellValues.SharedString))
{
int ssid = int.Parse(c.CellValue.Text);
string str = sst.ChildElements[ssid].InnerText;
string.Format("Shared string {0}: {1}", ssid, str);
}
else if (c.CellValue != null)
{
string.Format("Cell contents: {0}", c.CellValue.Text);
}
}
}
}
}
} /// <summary>
///
/// </summary>
/// <param name="filename"></param>
/// <param name="combox"></param>
/// <returns></returns>
public static DataTable getCombox(string filename, ComboBox combox)
{ DataTable dt = new DataTable();
List<SheetNameInfo> sheetinfo = new List<SheetNameInfo>();
sheetinfo = GetSheets(filename);
combox.DataSource = sheetinfo;
combox.ValueMember = "SheetName";
combox.DisplayMember = "SheetName";
//dt=ReadIdDataTable(filename, sheetinfo[0].SheetName);
return dt; }
/// <summary>
/// 读取工作表名 涂聚文注
/// EXCEL 2007版以上
/// </summary>
/// <param name="strFileName"></param>
/// <returns></returns>
public static List<SheetNameInfo> GetSheets(String strFileName)
{
string id = string.Empty;
// Fill this collection with a list of all the sheets.
List<SheetNameInfo> sheets = new List<SheetNameInfo>();
using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(strFileName, false))
{ WorkbookPart workbook = xlPackage.WorkbookPart;
Stream workbookstr = workbook.GetStream();
XmlDocument doc = new XmlDocument();
doc.Load(workbookstr);
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);
int k = 0;
foreach (XmlNode node in nodelist)
{
SheetNameInfo sheetNameInfo = new SheetNameInfo();
String sheetName = String.Empty;
sheetName = node.Attributes["name"].Value;
// id = node.Attributes["id"].Value;
sheetNameInfo.SheetID = int.Parse(node.Attributes["sheetId"].Value.ToString());
sheetNameInfo.Rid = node.Attributes["r:id"].Value;
sheetNameInfo.SheetName = sheetName;
sheets.Add(sheetNameInfo);
k++;
}
}
return sheets;
}
/// <summary>
///
/// </summary>
/// <param name="strFileName"></param>
/// <returns></returns>
public static List<String> GetStringSheets(String strFileName)
{ // Fill this collection with a list of all the sheets. List<String> sheets = new List<String>(); using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(strFileName, false))
{ WorkbookPart workbook = xlPackage.WorkbookPart; Stream workbookstr = workbook.GetStream(); XmlDocument doc = new XmlDocument(); doc.Load(workbookstr); XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable); nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI); XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);
foreach (XmlNode node in nodelist)
{ String sheetName = String.Empty; sheetName = node.Attributes["name"].Value; sheets.Add(sheetName); } }
return sheets; }
/// <summary>
///读取工作表名
/// </summary>
public class SheetNameInfo
{
private int _sheetId;
private string _sheetName; private string _rid; /// <summary>
///
/// </summary>
public int SheetID
{
get { return _sheetId; }
set { _sheetId = value; }
}
/// <summary>
///
/// </summary>
public string SheetName
{
get { return _sheetName; }
set { _sheetName = value; }
}
/// <summary>
///
/// </summary>
public string Rid
{
get { return _rid; }
set { _rid = value; }
}
}