public static string strNaneColumn(int totalColumn, ExcelWorksheet worksheet)
{
int iRow = 1;
string para = null;
for (int iCol = 0; iCol < totalColumn; iCol++)
{
if (worksheet.Cell(iRow, iCol + 1).Value != "")
{
string value = worksheet.Cell(iRow, iCol + 1).Value;
para += (iCol == totalColumn - 1) ? value : value + ',';
}
}
return para.Trim();
}
public static int countColumn(ExcelWorksheet worksheet)
{
int totalcell = 0;
do
{
totalcell++;
} while (worksheet.Cell(1, totalcell + 1).Value != "");
return totalcell;
}
///<summary>
/// Get a strongly type value from the cell
///</summary>
///<param name="sheet">Current worksheet</param>
///<param name="cellRow">Cell row</param>
///<param name="cellColumn">Cell column</param>
///<param name="defaultValue">The default value</param>
///<typeparam name="TValue">Type of value to expect</typeparam>
///<returns>The strongly type value</returns>
public static TValue GetCellValue <TValue>(this ExcelWorksheet sheet, int cellRow, int cellColumn, TValue defaultValue)
{
return(ConvertTo(sheet.Cell(cellRow, cellColumn).Value, defaultValue));
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1);
newCol.ColumnMax = column.ColumnMax;
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if (cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column=ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while(column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if(address.Start.Column==1 && address.End.Column==ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if(cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
/// <summary>
/// ExcelPackage组件方法生成Insert语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
public static void excelPackagePrintSQLLangInsertMulti(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "INSERT INTO ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 3)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
FileStream aFile = new FileStream("InsertMulti.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES
* ('526','17','我想','你好啊','2014-09-20 21:33:25','230'),
* ('527','18','不想','我好啊','2014-09-21 21:34:26','231');
*/
for (int i = 1; i < hangY; i++)
{
string outPrint = "";
if (i == 1)
{
outPrint = basicStr + myWorksheet.Cell(2, 1).Value + " (";
}
else
{
outPrint = outPrint + "(";
}
for (int j = 2; j < lieXX; j++)
{
if (i == 1)
{
if (j != lieXX - 1)
outPrint = outPrint + myWorksheet.Cell(1, j).Value + ",";
else
outPrint = outPrint + myWorksheet.Cell(1, j).Value + ") VALUES ";
}
else
{
outPrint = outPrint + "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
else
{
if (i != hangY - 1)
{
outPrint = outPrint + "),";
}
else
{ //末行加分号，表示所有插入语句结束
outPrint = outPrint + ");";
}
}
}
}
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
/// <summary>
/// ExcelPackage组件方法生成UpdateOnly语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangUpdateOnly(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "UPDATE ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 4)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
try
{
FileStream aFile = new FileStream("UpdateOnly.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* Excel File Likes：(Sheet Name = DataBase Table name = String "update_only")
tables_name name age sex
video_category 张松溪 22 1
video_category 宋远桥 33 2
video_category 俞岱岩 44 1
video_category 张三丰 55 1
video_category 殷梨亭 66 2
* UPDATE `update_only`.`video_category` SET age='22',sex='1' WHERE 'id'=
* (SELECT 'id' FROM `update_only`.`video_category` WHERE 'name'='张松溪');
* 减少部分语句，假设已经切换到当前数据库：
* UPDATE `video_category` SET age='22',sex='1' WHERE 'id'=
* (SELECT 'id' FROM `video_category` WHERE 'name'='张松溪');
*/
for (int i = 2; i < hangY; i++)
{
string outPrint;
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " SET ";
for (int j = 3; j < lieXX; j++)
{
outPrint = outPrint + myWorksheet.Cell(1, j).Value + "=" + "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
else
{
outPrint = outPrint + " ";
}
}
//假设主键名称为'id'
outPrint = outPrint + "WHERE '" + FormMain.primaryKeyName +
"'=" + "(SELECT '" + FormMain.primaryKeyName + "' FROM '" + myWorksheet.Cell(i, 1).Value + "' WHERE '"
+ myWorksheet.Cell(1, 2).Value + "'='" + myWorksheet.Cell(i, 2).Value + "');";
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch (Exception ex)
{
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
/// <summary>
/// ExcelPackage组件方法生成Update语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangUpdate(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "UPDATE ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 4)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
try
{
FileStream aFile = new FileStream("Update.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* UPDATE `kswiki2`.`wish` SET status='12',text='dsf' WHERE id='1';
*/
for (int i = 2; i < hangY; i++)
{
string outPrint;
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " SET ";
for (int j = 3; j < lieXX; j++)
{
outPrint = outPrint + myWorksheet.Cell(1, j).Value + "=" + "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
else
{
outPrint = outPrint + " ";
}
}
outPrint = outPrint + "WHERE " + myWorksheet.Cell(1, 2).Value + "=" + "'" + myWorksheet.Cell(i, 2).Value + "';";
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch (Exception ex)
{
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
/// <summary>
/// ExcelPackage组件方法生成Insert语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangInsertEachLineASentence(ExcelWorksheet myWorksheet)
{
int hangY = 0, lieXX = 0;
string eCellStr = "", langTop = "";
string basicStr = "INSERT INTO ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 3)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
FileStream aFile = new FileStream("InsertEach.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/**
* INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES
('526','17','我想','你好啊','2014-09-20 21:33:25','230');
* INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES
('527','18','不想','我好啊','2014-09-21 21:34:26','231');
* 同一个表中插入，每行只写一句话，并以分号结束，方便大量的数据多次、多进程处理。
**/
langTop = basicStr + myWorksheet.Cell(2, 1).Value + " (";
for (int j = 2; j < lieXX; j++)
{
langTop += myWorksheet.Cell(1, j).Value;
if (j != lieXX - 1)
{
langTop = langTop + ",";
}
}
//固定部分无须带入循环中生成
langTop += ") VALUES ("; //langTop = INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES (
for (int i = 2; i < hangY; i++)
{
string outPrint = "";
for (int j = 2; j < lieXX; j++)
{
outPrint += "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
}
//outPrint = '526','17','我想','你好啊','2014-09-20 21:33:25','230'
outPrint = langTop + outPrint + ");";
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
/// <summary>
/// ExcelPackage组件方法生成Delete语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangDelete(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "DELETE FROM ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 2)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
try
{
FileStream aFile = new FileStream("Delete.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* delete from tableA where statusB='12'and textC='dsf';
*/
for (int i = 2; i < hangY; i++)
{
string outPrint;
if (lieXX == 2)
{
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " where "
+ myWorksheet.Cell(1, 2).Value + "='" + myWorksheet.Cell(i, 2).Value + "';";
}
else//如果大于2，就用3列的，大于3列的表格也只考虑3列数据！
{
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " where "
+ myWorksheet.Cell(1, 2).Value + "='" + myWorksheet.Cell(i, 2).Value
+ "' AND " + myWorksheet.Cell(1, 3).Value + "='" + myWorksheet.Cell(i, 3).Value
+ "';";
}
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch (Exception ex)
{
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
/// <summary>
/// ExcelPackage组件方法生成将多列集合成两列。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackageDataTrimMerge(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eStrHang = "", eStrLie = "";
try
{
FileStream aFile = new FileStream("excelPackage-dataTrimMerge.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* * line A likes; 小胖A 男 20岁 宅 木讷
* output likes:
* 小胖A 男
* 小胖A 20岁
* 小胖A 宅
* 小胖A 木讷
*/
eStrHang = myWorksheet.Cell(hangY, 1).Value;
while (eStrHang != null && eStrHang != "")
{
string outPrint = "";
while (lieXX <= rawDataTableMaxRows)
{
eStrLie = myWorksheet.Cell(hangY, lieXX).Value;
if (eStrLie != null && eStrLie != "")
{
outPrint += eStrHang + "\t" + eStrLie + "\n"; //每行写一次不算多！
}
lieXX++;
}
hangY++;
lieXX = 1;
eStrHang = myWorksheet.Cell(hangY, 1).Value;
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch
{
throw new ApplicationException();
}
}
private static void excelPackageDataGroupMixedOne(ExcelWorksheet myWorksheet)
{
int hangY = 1;
string eStrHang = "", eStrNext = "";
try
{
FileStream aFile = new FileStream("excelPackage-dataGroupMixedOne.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* raw datas like:
* 小胖B 男
* 小胖B 20岁
* 小胖B 宅
* 小胖B 木讷
* After mix like:
* 小胖B 男 20岁 宅 木讷
*/
eStrNext = myWorksheet.Cell(hangY, 1).Value;
while (eStrNext != null && eStrNext != "")
{
string outPrint = "";
if (eStrNext == eStrHang)
{
outPrint += "\t" + myWorksheet.Cell(hangY, 2).Value;
hangY++;
// Write data to file.
sw.Write(outPrint);
if (myWorksheet.Cell(hangY, 1).Value != null)
{
eStrNext = myWorksheet.Cell(hangY, 1).Value;
}
else
break;
}
else
{
eStrHang = myWorksheet.Cell(hangY, 1).Value;
outPrint += "\t" + myWorksheet.Cell(hangY, 2).Value;
hangY++;
// Write data to file.
sw.Write("\n" + eStrHang + outPrint);
if (myWorksheet.Cell(hangY, 1).Value != null)
{
eStrNext = myWorksheet.Cell(hangY, 1).Value;
}
else
continue;
}
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch
{
throw new ApplicationException();
}
}
private static void CreateExcelFileContent(ExcelWorksheet sheet, List<PortData> portList)
{
var rowNum = 2;
foreach (var port in portList.Where(p => p.IsActive))
{
sheet.Cell(rowNum, 1).Value = port.TestStationId.ToString();
sheet.Cell(rowNum, 2).Value = port.ParameterList.Find(p => p.Id == ParameterId.PublicKey).RealValue.Replace("-", string.Empty);
sheet.Cell(rowNum, 3).Value = port.ParameterList.Find(p => p.Id == ParameterId.ManufacturerSerialNumber).RealValue ?? string.Empty;
sheet.Cell(rowNum, 4).Value = port.ParameterList.Find(p => p.Id == ParameterId.ServerId).RealValue.Replace("-", string.Empty);
var enr = port.ParameterList.Find(p => p.Id == ParameterId.Eigentumsnummer).TargetValue ?? string.Empty;
sheet.Cell(rowNum, 5).Value = string.Format("{0}", enr);
sheet.Cell(rowNum, 6).Value = port.ParameterList.Find(p => p.Id == ParameterId.Typ).TargetValue ?? string.Empty;
sheet.Cell(rowNum, 7).Value = port.ParameterList.Find(p => p.Id == ParameterId.PinCode).TargetValue ?? string.Empty;
rowNum++;
}
}
private static void CreateExcelFileHeader(ExcelWorksheet sheet)
{
sheet.Cell(1, 1).Value = "Prüfplatznummer";
sheet.Cell(1, 2).Value = "PublicKey";
sheet.Cell(1, 3).Value = "Fabriknummer";
sheet.Cell(1, 4).Value = "Server-ID";
sheet.Cell(1, 5).Value = "Eigentumsnummer";
sheet.Cell(1, 6).Value = "Typ";
sheet.Cell(1, 7).Value = "Pin";
}
