//-------------------------------------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); } }