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"; }