public void CreateOrUpdateRowAt2() { //null,ok. position 0,0 [-1,-1],[2,3] ,[1,1] string path = "C:\\testfile\\testrowdata.xlsx"; try { using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, "Report", true)) { Assert.AreEqual(incOpenExcel.IsValidate(), true); DataTable dataTable = Common.incUnitTest.GetDatatableCustomCount(5); Dictionary <uint, uint> style = new Dictionary <uint, uint>(); style.Add(5, 1); incOpenExcel.CreateOrUpdateRowAt("Report", dataTable.Rows[0], 4, 4, style); incOpenExcel.CreateOrUpdateRowAt("Report", null, 1, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", dataTable.Rows[1], 2, 2, null); incOpenExcel.CreateOrUpdateRowAt("Report", dataTable.Rows[2], 3, 3, null); incOpenExcel.CreateOrUpdateRowAt("Report", dataTable.Rows[3], 1, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", dataTable.Rows[4], 2, 2, null); } } catch (Exception e) { Debug.Print(e.ToString()); } }
public void CreateOrUpdateRowsAt1() { // table null, 1, 5,2 replace 5. string path = "C:\\testfile\\testtabledata1.xlsx"; try { using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, "Report", true)) { Assert.AreEqual(incOpenExcel.IsValidate(), true); DataTable dataTable = null;// Common.incUnitTest.GetDatatableCustomCount(5); //excelh CreateOrUpdateRowsAt(incOpenExcel, "Report", dataTable, 2, 2,null); dataTable = Common.incUnitTest.GetDatatableCustomCount(5); //incOpenExcel.CreateOrUpdateRowsAt("Report", dataTable, 2, 2, null); dataTable = Common.incUnitTest.GetDatatableCustomCount(2); //incOpenExcel.CreateOrUpdateRowsAt("Report", dataTable, 3, 1, null); } } catch (Exception e) { Debug.Print(e.ToString()); } }
public static bool CreateReport(string path, DataTable dataTable, List <int> totalColumn) { using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, SHEETNAME, true)) { if (dataTable != null) { //init variable uint startRowNo = 1; uint startColumnNo = 2; uint writingRowNo = startRowNo; bool hasTotal = totalColumn == null ? false : totalColumn.Count > 0 ? true : false; //insert databable CreateOrUpdateRowsAt(incOpenExcel, SHEETNAME, incOpenExcel.defaultCellStyle, dataTable, startRowNo, startColumnNo, null); writingRowNo += (uint)dataTable.Rows.Count + 1; //insert total InsertSpecialPart_reportTotal.FillSomeData(incOpenExcel, dataTable, SHEETNAME, totalColumn, ref writingRowNo, incOpenExcel.defaultCellStyle); //insert dataFlag writingRowNo--; SetDataFlag(startRowNo, writingRowNo, incOpenExcel, SHEETNAME); } } return(true); }
private static void GetDataFlag(Common.IncOpenExcel myexcel, out uint startRowNo, out uint endRowNo, string sheetName) { startRowNo = myexcel.FindStringInColumn(sheetName, STRING_DATASTART, 1); endRowNo = myexcel.FindStringInColumn(sheetName, STRING_DATAEND, 1); if (startRowNo == 0 && endRowNo == 0)//can't find. { startRowNo = endRowNo = myexcel.FindStringInColumn(sheetName, STRING_DATASTART + STRING_DATAEND, 1); } }
public void T10_CreateOrUpdateCell() { string path = "C:\\testfile\\bll_templateUpdateCell.xlsx"; DataTable dataTable = Common.incUnitTest.GetDatatableCustomCount(5); using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, SHEETNAME, true)) { //incOpenExcel.CreateOrUpdateRowsAt(SHEETNAME, dataTable, 1, 2,null); incOpenExcel.CreateOrUpdateCellAt(SHEETNAME, 1, 1, typeof(string), "_start"); } }
private static void SetDataFlag(uint startRow, uint endrow, Common.IncOpenExcel myexcel, string sheetName) { if (startRow == endrow) { myexcel.CreateOrUpdateCellAt(sheetName, startRow, 1, typeof(string), STRING_DATASTART + STRING_DATAEND); } else { myexcel.CreateOrUpdateCellAt(sheetName, startRow, 1, typeof(string), STRING_DATASTART); myexcel.CreateOrUpdateCellAt(sheetName, endrow, 1, typeof(string), STRING_DATAEND); } }
//不是立即移动,而是得到xml,再附加文件的末尾,并附带一个移动到的行编号 public static bool move(string path, DataTable dataTable) { bool res = false; using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, SHEETNAME, true)) { //incOpenExcel.CreateOrUpdateRowsAt(SHEETNAME, dataTable, 1, 2, null); List <string> rowsXmls = incOpenExcel.GetRowsXml(SHEETNAME, 2, 4); incOpenExcel.DeleteRows(SHEETNAME, 2, 4); incOpenExcel.MoveRows(SHEETNAME, rowsXmls, 20); } return(res); }
public void CreateFile() { //null,"", "abc" "avadf\fdsa.word" "aa.xlsx" string[] files = { null, "", "aa", "C:\\testfile\\empty1.world", "C:\\testfileaaaaa\\empty1.world", "C:\\testfile\\empty1.xlsx", "C:\\testfile\\empty1.xlsx" }; for (int i = 0; i < files.Count(); i++) { try { using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(files[i], "Report", true)) { Assert.AreEqual(incOpenExcel.IsValidate(), true); } } catch (Exception e) { Debug.Print(e.ToString()); } } }
public static bool UpdateReport(string path, DataTable dataTable, List <int> totalColumn) { using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, SHEETNAME, false)) { if (dataTable != null) { //0.get style of report 1.save bottom 2.delete all from data 3.append data 4. get offset and change boxxom 5.append bottom ////init variable uint startRowNo, endRowNo, writingRowNo; uint startColumnNo = 2; GetDataFlag(incOpenExcel, out startRowNo, out endRowNo, SHEETNAME); writingRowNo = startRowNo; bool hasTotal = totalColumn == null ? false : totalColumn.Count > 0 ? true : false; bool isHiddenTitle = false; Dictionary <uint, uint> titleStyle = Common.IncOpenExcel.getRowStyles(incOpenExcel.GetRow(SHEETNAME, startRowNo), out isHiddenTitle); //delete predata List <string> bottomXmls = incOpenExcel.GetRowsXml(SHEETNAME, endRowNo + 1, uint.MaxValue); incOpenExcel.DeleteRows(SHEETNAME, startRowNo, uint.MaxValue); //insert databable CreateOrUpdateRowsAt(incOpenExcel, SHEETNAME, incOpenExcel.defaultCellStyle, dataTable, startRowNo, startColumnNo, titleStyle, isHiddenTitle); writingRowNo += (uint)dataTable.Rows.Count + 1; //insert total InsertSpecialPart_reportTotal.FillSomeData(incOpenExcel, dataTable, SHEETNAME, totalColumn, ref writingRowNo, incOpenExcel.defaultCellStyle); //insert dataFlag writingRowNo--; SetDataFlag(startRowNo, writingRowNo, incOpenExcel, SHEETNAME); //append bottom writingRowNo++; incOpenExcel.MoveRows(SHEETNAME, bottomXmls, writingRowNo); //clear value which contain formule incOpenExcel.ClearFormulaCache(SHEETNAME); } } return(true); }
public void CreateOrUpdateRowAt() { //null,ok. //position 0,0 [-1,-1],[2,3] ,[1,1] string path = "C:\\testfile\\testjustrow.xlsx"; using (Common.IncOpenExcel incOpenExcel = new Common.IncOpenExcel(path, "Report", true)) { Assert.AreEqual(incOpenExcel.IsValidate(), true); incOpenExcel.CreateOrUpdateRowAt("Report", null, 4, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", null, 1, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", null, 2, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", null, 3, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", null, 1, 1, null); incOpenExcel.CreateOrUpdateRowAt("Report", null, 5, 1, null); } unzip(path); }
private static bool CreateOrUpdateRowsAt(Common.IncOpenExcel excelFile, string sheetName, Common.IncOpenExcel.DefaultCellStyle defaultCellStyle, DataTable dataTable, uint rowNo, uint columnNo, Dictionary <uint, uint> titleStyle, bool titlehidden = false) { if (excelFile != null && sheetName != null && dataTable != null && defaultCellStyle != null) { DataTable columnsTable = Common.IncOpenExcel.GetColumnsNames(dataTable); if (titleStyle == null) { titleStyle = Common.IncOpenExcel.getRowStyles(columnsTable.Columns, columnNo, 3, defaultCellStyle);//3:black style } excelFile.CreateOrUpdateRowAt(sheetName, columnsTable.Rows[0], rowNo, columnNo, titleStyle, titlehidden); for (int i = 0; i < dataTable.Rows.Count; i++) { Dictionary <uint, uint> tempRowStyle = Common.IncOpenExcel.getRowStyles(dataTable.Columns, columnNo, 2, defaultCellStyle);//2:normal style excelFile.CreateOrUpdateRowAt(sheetName, dataTable.Rows[i], rowNo + (uint)(i + 1), columnNo, tempRowStyle); } } return(true); }