https://openxmlexporttoexcel.codeplex.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; }
}
}
