//-------------------------------------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);
}
}
//doc excel
public void LoadExcelSheets()
{
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)
{
TabPage sheetPage = new TabPage(sheet.Name); // Tên Sheet
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;
// 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)
{
}
}
sheetPage.Controls.Add(dgvCells);
tabControl1.TabPages.Add(sheetPage);
}
}
/// <summary>
/// Open workbook from a file path.
/// </summary>
/// <param name="file"></param>
public static Workbook Open(FileStream stream)
{
CompoundDocument doc = CompoundDocument.Read(stream);
if (doc == null)
{
throw new Exception("Invalid Excel file");
}
byte[] bookdata = doc.GetStreamData("Workbook");
return(WorkbookDecoder.Decode(new MemoryStream(bookdata)));
}
/// <summary>
/// Open workbook from a file stream.
/// </summary>
/// <param name="file"></param>
public void Open(Stream file)
{
CompoundDocument doc = CompoundDocument.Read(file);
if (doc == null)
{
throw new Exception("Invalid Excel file");
}
byte[] bookdata = doc.GetStreamData("Workbook");
this.Read(new MemoryStream(bookdata));
}
public static Workbook Open(string file)
{
CompoundDocument compoundDocument = CompoundDocument.Read(file);
if (compoundDocument == null)
{
throw new Exception("Invalid Excel file");
}
byte[] streamData = compoundDocument.GetStreamData("Workbook");
return(WorkbookDecoder.Decode(new MemoryStream(streamData)));
}
/// <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();
}
}
}
/// <summary>
/// 初始化
/// </summary>
public void Intialize()
{
this.InitializeLog();
this.ErrLock[0] = new object();
this.ErrLock[1] = new object();
this.ErrLock[2] = new object();
WriteLog(MsgLevel.Info, "机器启动...");
WriteLog(MsgLevel.Info, "正在载入相关字符定义");
this.Start();
if (!File.Exists("./Configure/机器字符定义.xls"))
{
WriteLog(MsgLevel.Info, "字符定义载入失败,没有找到定义文件");
return;
}
// 读取Excel进行设置ErrorCode的定义
CompoundDocument doc = CompoundDocument.Open("./Configure/机器字符定义.xls");
byte[] bookdata = doc.GetStreamData("Workbook");
if (bookdata == null)
{
return;
}
using (var ms = new MemoryStream(bookdata))
{
Workbook book = WorkbookDecoder.Decode(ms);
Worksheet sheet = book.Worksheets[0];
int rowCount = sheet.Cells.LastRowIndex + 1;
int colCount = sheet.Cells.LastColIndex + 1;
for (int i = 0; i < colCount; ++i)
{
DataColumn dt = new DataColumn(sheet.Cells[0, i].StringValue);
stringDB.Columns.Add(dt);
}
string[] rowValue = new string[colCount];
for (int row = 1; row < rowCount; row++)
{
for (int col = 0; col < colCount; col++)
{
rowValue[col] = sheet.Cells[row, col].StringValue;
}
stringDB.Rows.Add(rowValue);
}
}
WriteLog(MsgLevel.Info, "字符定义载入成功");
}
private void LoadExcelSheets()
{
byte[] bookdata = doc.GetStreamData("Workbook");
if (bookdata == null)
{
return;
}
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
//ExtractImages(book, @"C:\Images");
tabControlSheets.TabPages.Clear();
foreach (Worksheet sheet in book.Worksheets)
{
TabPage sheetPage = new TabPage(sheet.Name);
DataGridView dgvCells = new DataGridView();
dgvCells.Dock = DockStyle.Fill;
dgvCells.RowCount = sheet.Cells.LastRowIndex + 1;
dgvCells.ColumnCount = sheet.Cells.LastColIndex + 1;
// 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)
{
}
}
foreach (KeyValuePair <Pair <int, int>, Picture> cell in sheet.Pictures)
{
int rowIndex = cell.Key.Left;
int colIndex = cell.Key.Right;
if (dgvCells.RowCount < rowIndex + 1)
{
dgvCells.RowCount = rowIndex + 1;
}
if (dgvCells.ColumnCount < colIndex + 1)
{
dgvCells.ColumnCount = colIndex + 1;
}
dgvCells[colIndex, rowIndex].Value = String.Format("<Image,{0}>", cell.Value.Image.FileExtension);
}
sheetPage.Controls.Add(dgvCells);
tabControlSheets.TabPages.Add(sheetPage);
}
}