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