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;
}
}
//-------------------------------------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);
}
}
/// <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)));
}
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();
}
}
}
public int returnlastrow(string path)
{
getopen(path);
byte[] bookdata = doc.GetStreamData("Workbook"); // Doc Theo Byte
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
Worksheet sheet = book.Worksheets[0];
int dong = sheet.Cells.LastRowIndex;
doc.Close();
return(dong);
}
/// <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, "字符定义载入成功");
}
//------------------Lay Gia Tri o dùng voi ham getopen -------------------------------------------
public string getcell(int i, int j)
{
int dong, cot;
dong = Rowcount;
cot = ColumnCount;
//-------------------------------------------------------------------
byte[] bookdata = doc.GetStreamData("Workbook");
if (bookdata == null)
{
return(null);
}
Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
Worksheet sheet = book.Worksheets[0];
//---------------------------------------------------------------------
string ID = sheet.Cells[i, j].StringValue;
doc.Close();
return(ID);
}
private void _btSave_Click(object sender, EventArgs e)
{
if (doc == null)
{
return;
}
string file = FileSelector.BrowseFileForSave(FileType.All);
if (file == null)
{
return;
}
using (CompoundDocument newDoc = CompoundDocument.Create(file))
{
foreach (string streamName in doc.RootStorage.Members.Keys)
{
newDoc.WriteStreamData(new string[] { streamName }, doc.GetStreamData(streamName));
}
byte[] bookdata = doc.GetStreamData("Workbook");
if (bookdata != null)
{
if (workbook == null)
{
workbook = WorkbookDecoder.Decode(new MemoryStream(bookdata));
}
MemoryStream stream = new MemoryStream();
//WorkbookEncoder.Encode(workbook, stream);
BinaryWriter writer = new BinaryWriter(stream);
foreach (Record record in workbook.Records)
{
record.Write(writer);
}
writer.Close();
newDoc.WriteStreamData(new string[] { "Workbook" }, stream.ToArray());
}
newDoc.Save();
}
}
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);
}
}
