//-------------------------------------hiện thị ra man hinh noi dung cua excel -------------
public DataGridView loadexcel()
{
try
{
byte[] bookdata = doc.GetStreamData("Workbook"); // Doc Theo Byte
if (bookdata == null)
{
return(null);
}
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
Worksheet sheet = book.Worksheets[0];
TabPage sheetPage = new TabPage(); // Tên Sheet
dgvCells = new DataGridView(); // Tao Moi Datagridview
dgvCells.Dock = DockStyle.Fill;
dgvCells.RowCount = sheet.Cells.LastRowIndex + 30;
this.Rowlastindex = sheet.Cells.LastRowIndex;
dgvCells.ColumnCount = 500;
this.Collastindex = sheet.Cells.LastColIndex;
// tranverse cells
foreach (Pair <Pair <int, int>, Cell> cell in sheet.Cells)
{
dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
if (cell.Right.Style.BackColor != Color.White)
{
dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
}
}
// tranvers rows by Index
for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
Row row = sheet.Cells.GetRow(rowIndex);
for (int colIndex = row.FirstColIndex; colIndex <= 500; colIndex++)
{
Cell cell = row.GetCell(colIndex);
}
}
// tranvers rows directly
foreach (KeyValuePair <int, Row> row in sheet.Cells.Rows)
{
foreach (KeyValuePair <int, Cell> cell in row.Value)
{
}
}
doc.Close();
return(dgvCells);
}
catch
{
return(null);
}
}
private void exitXToolStripMenuItem_Click(object sender, EventArgs e)
{
if (IsOpened)
{
doc.Close();
}
this.Close();
}
public void LoadExcelSheets(string filepath)
{
CompoundDocument _doc = CompoundDocument.Open(filepath);
byte[] bookdata = _doc.GetStreamData("Workbook"); // Doc Theo Byte
if (bookdata == null)
{
return;
}
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
foreach (Worksheet sheet in book.Worksheets)
{
ExcelTabPage sheetPage = new ExcelTabPage(sheet.Name); // Tên Sheet
sheetPage.FileName = filepath;
dgvCells = new DataGridView(); // Tao Moi Datagridview
dgvCells.Dock = DockStyle.Fill;
//dgvCells.RowCount = sheet.Cells.LastRowIndex + 1;
//dgvCells.ColumnCount = sheet.Cells.LastColIndex + 1;
dgvCells.RowCount = 500;
dgvCells.ColumnCount = 500;
dgvCells.KeyUp += dgvCells_KeyUp;
// tranverse cells
foreach (Pair <Pair <int, int>, Cell> cell in sheet.Cells)
{
dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
if (cell.Right.Style.BackColor != Color.White)
{
dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
}
}
// tranvers rows by Index
for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
Row row = sheet.Cells.GetRow(rowIndex);
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
}
}
// tranvers rows directly
foreach (KeyValuePair <int, Row> row in sheet.Cells.Rows)
{
foreach (KeyValuePair <int, Cell> cell in row.Value)
{
}
}
_doc.Close();
sheetPage.Controls.Add(dgvCells);
tabControl1.TabPages.Add(sheetPage);
tabControl1.SelectedTab = sheetPage;
}
}
public void Save(string file)
{
CompoundDocument doc = CompoundDocument.Create(file);
MemoryStream stream = new MemoryStream();
WorkbookEncoder.Encode(this, stream);
doc.WriteStreamData(new string[] { "Workbook" }, stream.ToArray());
doc.Save();
doc.Close();
}
/// <summary>
/// parse the source file
/// </summary>
/// <param name="path">the file path</param>
/// <returns>return true if parse successfully</returns>
private bool Parse(string path)
{
try
{
CompoundDocument doc = CompoundDocument.Load(path);
if (doc == null)
{
throw new InvalidOperationException(Constants.Messages.Error_ExcelFileNotFound);
}
Lines.Clear();
byte[] bookdata = doc.GetStreamData("Workbook");
if (bookdata == null)
{
throw new InvalidOperationException(Constants.Messages.Error_ExcelFileNoWorkbook);
}
Workbook workbook = WorkbookDecoder.Decode(new MemoryStream(bookdata));
if (workbook.Worksheets.Count == 0)
{
throw new InvalidOperationException(Constants.Messages.Error_ExcelFileNoWorksheet);
}
Worksheet sheet = workbook.Worksheets[0];
m_WorksheetName = sheet.Name;
for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
SourceLine line = new SourceLine();
Row row = sheet.Cells.GetRow(rowIndex);
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
line.Columns.Add(cell.StringValue);
}
Lines.Add(line);
}
doc.Close();
return(true);
}
catch
{
Lines.Clear();
throw;
}
finally
{
if (this.FileParsed != null)
{
this.FileParsed();
}
}
}
public void Save(Stream s)
{
BinaryWriter bw = new BinaryWriter(s);
//Writing Column Naow
int TotalRows = this.Worksheets[0].Cells.LastRowIndex;
for (int i = TotalRows + 1; i < TotalRows + 50; i++)
{
for (int ColumnCount = 0; ColumnCount < 5; ColumnCount++)
{
this.Worksheets[0].Cells[i, ColumnCount] = new Cell("");
}
}
CompoundDocument doc = CompoundDocument.Create(s, bw);
MemoryStream stream = new MemoryStream();
WorkbookEncoder.Encode(this, stream);
doc.WriteStreamData(new string[] { "Workbook" }, stream.ToArray());
doc.Save();
doc.Close();
}