PDA

View Full Version : آموزش: استفاده از اكسل در سي شارپ بدون نصب آفيس و اكسل



naser61nsn
یک شنبه 12 مهر 1394, 21:41 عصر
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public static DataTable dt = new DataTable();
public static DataGridView dg = new DataGridView();
public static int row, i, j, k, l;
public static List<Cell> parsedCells = new List<Cell>();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
dg.Location = new Point(100, 50);
dg.Size = new Size(800, 400);
tabPage1.Controls.Add(dg);
Ma();
}
public static void Ma()
{
string fileName = @"c:\Book1.xlsx";
Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);

PackagePartCollection allParts = xlsxPackage.GetParts();
PackagePart sharedStringsPart = allParts.ElementAtOrDefault(1);

XElement sharedStringsElement = XElement.Load(XmlReader.Create(sharedStringsPart.G etStream()));
Dictionary<int, string> sharedStrings = new Dictionary<int, string>();

ParseSharedStrings(sharedStringsElement, sharedStrings);
XElement worksheetElement = GetWorksheet(1, allParts);

IEnumerable<XElement> cells = from c in worksheetElement.Descendants(ExcelNamespaces.excel Namespace + "c") select c;

foreach (XElement cell in cells)
{
string cellPosition = cell.Attribute("r").Value;
int index = IndexOfNumber(cellPosition);
row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));
string column = cellPosition.Substring(index,0);
if (cell.HasElements)
if (cell.Attribute("t") != null && cell.Attribute("t").Value == "s")
{
int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.e xcelNamespace + "v").Single().Value);
parsedCells.Add(new Cell(column, row, sharedStrings[valueIndex]));
}
else
{
string value = cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value;
parsedCells.Add(new Cell(column, row, value));
}
else parsedCells.Add(new Cell(column, row, ""));
}
for (i = 0; i < row; i++) dt.Rows.Add();
for (j = 0; j < parsedCells.Count / row; j++) dt.Columns.Add(j.ToString());

dg.DataSource = dt;

for (i = 0; i < row; i++)
{
for (j = 0; j < parsedCells.Count / row; j++)
{
dg.Rows[i].Cells[j].Value = parsedCells[k].Data.ToString(); k++;
}
}
}
private static void ParseSharedStrings(XElement SharedStringsElement, Dictionary<int, string> sharedStrings)
{
IEnumerable<XElement> sharedStringsElements = from s in SharedStringsElement.Descendants(ExcelNamespaces.e xcelNamespace + "t") select s;
int Counter = 0;

foreach (XElement sharedString in sharedStringsElements)
{
sharedStrings.Add(Counter, sharedString.Value);
Counter++;
}
}
private static XElement GetWorksheet(int worksheetID, PackagePartCollection allParts)
{
PackagePart worksheetPart = (from part in allParts where part.Uri.OriginalString.Equals(String.Format("/xl/worksheets/sheet{0}.xml", worksheetID)) select part).Single();
return XElement.Load(XmlReader.Create(worksheetPart.GetSt ream()));
}
private static int IndexOfNumber(string value)
{
for (int counter = 0; counter < value.Length; counter++) if (char.IsNumber(value[counter])) return counter;
return 0;
}
internal static class ExcelNamespaces
{
internal static XNamespace excelNamespace = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
internal static XNamespace excelRelationshipsNamepace = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
public class Cell
{
public Cell(string column, int row, string data)
{
this.Column = column; this.Row = row; this.Data = data;
}
public override string ToString()
{
return string.Format("{0}:{1} - {2}", Row, Column, Data);
}
public string Column { get; set; }
public int Row { get; set; }
public string Data { get; set; }
}
}
}

در فولدر سيستم 32 وينور ، فايل windowsbase.dll سرچ و به عنوان رفرنس جديد به برنامه اضافه شود تا فضاي using System.IO.Packaging; شناسايي گردد .