static void ShowWindow()
{
ExcelEditor window = EditorWindow.GetWindowWithRect <ExcelEditor>(new Rect(0, 0, 800, 400));
window.Show();
string path = Application.dataPath + "/Test/Test3.xlsx";
Excel xls = ExcelHelper.LoadExcel(path);
xls.ShowLog();
window.Show(xls);
}
static void ShowWindow()
{
ExcelEditor window = EditorWindow.GetWindowWithRect <ExcelEditor>(new Rect(0, 0, 800, 400));
window.Show();
string path = System.IO.Path.Combine(Application.streamingAssetsPath, "Test3.xlsx");
Excel xls = ExcelHelper.LoadExcel(path);
xls.ShowLog();
window.Show(xls);
}
public static void Run()
{
try
{
DocumentEditor editor = new ExcelEditor();
var doc = editor.OpenDocument("abc.xls");
doc.Close();
doc = editor.OpenDocument("abc.xlx");
}
catch (InvalidOperationException e)
{
Console.WriteLine(e.Message);
}
}
private void xlsxReplace(List <Pixel> matches, Color newColor)
{
var isTransparent = newColor.IsTransparent();
var newColorOle = newColor.ToOle();
using var editor = new ExcelEditor(Pattern.InputFile)
{
CreateBackupFile = CreateBackupFile
};
foreach (var px in matches)
{
editor
.GetCell(px.RowNumber, px.ColumnNumber)
.SetColor(newColorOle, isTransparent);
}
}
public void TestSaveExcel()
{
string ExcelPath = @"D:\ConceptFlower Test\Epass Stage 1 & 2 automation tool.xlsx";
ExcelEditor excelHandel = new ExcelEditor();
List <WhiteListcs> wlist = new List <WhiteListcs>();
WhiteListcs model = new WhiteListcs {
PM_AC = "PC", Membership_No = "12345678-123456789", ERID = "21456789", Case_Number = "5454579859"
, Name_in_AS400 = "test", Name_in_XML = "test", Name_Pass = "******", Clean_Case = "Y", Unclean_Reason = new StringBuilder("sdfasdfsfds"), Signature_Pass = "******"
, Withdraw_NT_PM = "NT", Process_result = "Process"
};
wlist.Add(model);
var excel = excelHandel.Open(ExcelPath);
DataTable dt = ListToDatatableHelper.ToDataTable <WhiteListcs>(wlist);
excel.SaveDataTableToExcel(dt, ExcelPath, "White List");
}
public static void NaiveReplace(string inputFile, Color oldColor, Color newColor, bool createBackupFile = false)
{
if (oldColor.ReplacementIsInvalid(newColor))
{
return;
}
var newColorOle = newColor.ToOle();
var isTransparent = newColor.IsTransparent();
bool isMatch(Range c) => ColorTranslator.FromOle((int)c.Interior.Color).IsEquivalent(oldColor);
using var editor = new ExcelEditor(inputFile)
{
CreateBackupFile = createBackupFile
};
editor.Iterate(isMatch, cell => cell.SetColor(newColorOle, isTransparent));
}
private static void Main(string[] args)
{
//Example A from A.xlsx
IExcelEditor excelA = new ExcelEditor("../../A.xlsx");
excelA.Set("name", "Sara");
excelA.Set("age", 123);
excelA.Save("../../A_result.xlsx");
//Example B from B.xlsx
IExcelEditor excelB = new ExcelEditor("../../B.xlsx");
excelB.Set("s",
new[]
{
new
{
Name = "Tommy",
Age = 12 as int?
},
new
{
Name = "Philips",
Age = 13 as int?
},
new
{
Name = "Sara",
Age = null as int?
}
});
excelB.Save("../../B_result.xlsx");
//Example C from C.xlsx
IExcelEditor excelC = new ExcelEditor("../../C.xlsx");
excelC.Set("s",
new[]
{
new
{
Name = "Tommy",
Age = 12
},
new
{
Name = "Philips",
Age = 13
}
},
false);
excelC.UpdateFormula();
excelC.Save("../../C_result.xlsx");
//Example D
var excelD = new ExcelFile();
excelD.Sheet("D sheet");
excelD.Row().Cell("Tommy").Cell(12);
excelD.Row().Cell("Philips").Cell(13);
excelD.Save("../../D_result.xls");
//Example E
var excelE = new ExcelFile(true);
excelE.Sheet("E sheet");
excelE.Row(25, excelE.NewStyle().Background(HSSFColor.Yellow.Index)).Empty(2).Cell("Tommy");
excelE.Row(15).Empty().Cell(12).Cell(13, excelE.NewStyle().Color(HSSFColor.Red.Index));
excelE.Save("../../E_result.xlsx");
//Example F from F.xlsx
foreach (var sheet in ExcelUtils.New("../../F.xlsx", FileMode.Open, FileAccess.Read).AsEnumerable())
{
foreach (var row in sheet.AsEnumerable())
{
if (row == null)
{
continue;
}
foreach (var cell in row.AsEnumerable())
{
if (cell == null)
{
Console.WriteLine("null");
}
else
{
switch (cell.CellType)
{
case CellType.Blank:
Console.WriteLine("blank");
break;
case CellType.Boolean:
Console.WriteLine(cell.BooleanCellValue);
break;
case CellType.Numeric:
Console.WriteLine(cell.NumericCellValue);
break;
case CellType.String:
Console.WriteLine(cell.StringCellValue);
break;
}
}
}
}
}
Console.Read();
}
public void readexcel()
{
List <WhiteListcs> wlist = new List <WhiteListcs>();
ExcelEditor excelHandel = new ExcelEditor();
//int index = 1;
//WhiteListcs w1 = new WhiteListcs();
//w1.Name_in_AS400 = "test";
//w1.Case_Number = "5454579859";
//w1.Clean_Case = "Y";
//w1.ERID = "21456789";
//w1.ExtentionNo = "81634";
//w1.Name_Pass = "******";
//w1.Membership_No = "12345678-123456789";
//w1.PM_AC = "PC";
//w1.Signature_Pass = "******";
//w1.Unclean_Reason.Append("sfdtrtyrturtutyuityurt");
//w1.Withdraw_NT_PM = "NT";
//w1.Name_in_XML = "test";
//wlist.Add(w1);
//WhiteListcs w2 = new WhiteListcs();
//w2.Name_in_AS400 = "test";
//w2.Case_Number = "5454579859";
//w2.Clean_Case = "Y";
//w2.ERID = "21456789";
//w2.ExtentionNo = "81634";
//w2.Name_Pass = "******";
//w2.Membership_No = "12345678-123456789";
//w2.PM_AC = "PC";
//w2.Signature_Pass = "******";
//w2.Unclean_Reason.Append("sfdtrtyrturtutyuityurt");
//w2.Withdraw_NT_PM = "NT";
//w2.Name_in_XML = "test";
//wlist.Add(w2);
//if (wlist.Count > 0)
//{
// var excel = excelHandel.Open(@"E:\Epass Stage 1 & 2 automation tool.xlsx");
// DataTable dt = ListToDatatableHelper.ToDataTable<WhiteListcs>(wlist);
// excel.SaveDataTableToExcel(dt, @"E:\Epass Stage 1 & 2 automation tool.xlsx", "White List");
//}
List <ExcelWhlist> ewlist = new List <ExcelWhlist>();
ExcelWhlist ew = new ExcelWhlist();
ew.Name_in_AS400 = "test";
ew.Case_Number = "5454579859";
ew.Clean_Case = "Y";
ew.ERID = "21456789";
//ew.ExtentionNo = "81634";
ew.Name_Pass = "******";
ew.Membership_No = "12345678-123456789";
ew.PM_AC = "PC";
ew.Signature_Pass = "******";
ew.Unclean_Reason = "fdsdsdsd";
ew.Withdraw_NT_PM = "NT";
ew.Name_in_XML = "test";
ew.RowIndex = "2";
ew.Process_result = "Processed";
//ew.Message_Mark = "Y";
ewlist.Add(ew);
if (ewlist.Count > 0)
{
var excel = excelHandel.Open(@"E:\Epass Stage 1 & 2 automation tool.xlsx");
excel.SetSpecifiedToExcel(ewlist, @"E:\Epass Stage 1 & 2 automation tool.xlsx", "White List");
}
}
private void Click_ExportExcelFile(object sender, RoutedEventArgs e)
{
ExcelEditor.ToExcelFile();
}
