public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
uint headerRowIndex = GetStartRowBound();
ushort firstColumnIndex = GetFirstNonEmptyColumn();
// есть пустые строки до начала данных
if (headerRowIndex != 1)
{
headerRowIndex--;
if (this.startRowBound != null)
startRowBound--;
}
else
{
Range headerCell = worksheet.Cells[1, 1];
headerCell.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown);
if (this.endRowBound != null)
this.endRowBound++;
}
int counter = 0;
for (ushort i = firstColumnIndex; i < firstColumnIndex + ColumnHeaders.Count; i++)
{
Range cell = worksheet.Cells[headerRowIndex, i];
cell.Value = ColumnHeaders[counter++];
}
return this;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Range cell = worksheet.Cells[3, columnIdentifier];
cell.Value = "ABCD";
return null;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Range cell = worksheet.Cells[3, columnIdentifier];
cell.Value = "ABCD";
return(null);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
if (CheckDeserialization())
{
FindBoundsAndProcess();
}
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
if (CheckDeserialization())
{
FindBoundsAndProcess();
}
return this;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
worksheet.get_Range("A" + RowIndex.ToString()).EntireRow.Delete();
// так как мы не знаем, что идёт после удаляемой строки,
// то не можем определить, какой строкой кончаются данные
this.endRowBound = null;
return this;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
worksheet.get_Range("A" + RowIndex.ToString()).EntireRow.Delete();
// так как мы не знаем, что идёт после удаляемой строки,
// то не можем определить, какой строкой кончаются данные
this.endRowBound = null;
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
CheckDeserialization();
Range column = worksheet.Columns[columnIdentifier];
column.EntireColumn.Delete();
this.endColumnBound = null;
return this;
}
protected void Init(TransformCommand another)
{
if (another != null)
{
this.endColumnBound = another.endColumnBound;
this.endRowBound = another.endRowBound;
this.excel = another.excel;
this.sheetNumber = another.sheetNumber;
this.startColumnBound = another.startColumnBound;
this.startRowBound = another.startRowBound;
this.workbook = another.workbook;
}
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
CheckDeserialization();
Range column = worksheet.Columns[columnIdentifier];
column.EntireColumn.Delete();
this.endColumnBound = null;
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
while (excel.WorksheetFunction.CountA(worksheet.get_Range("A1", "A1").EntireColumn) == 0)
{
worksheet.get_Range("A1", "A1").EntireColumn.Delete();
this.startColumnBound = 1;
this.endColumnBound = null;
}
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
while (excel.WorksheetFunction.CountA(worksheet.get_Range("A1", "A1").EntireColumn) == 0)
{
worksheet.get_Range("A1", "A1").EntireColumn.Delete();
this.startColumnBound = 1;
this.endColumnBound = null;
}
return this;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
while (excel.WorksheetFunction.CountA(worksheet.get_Range("A1", "A1").EntireRow) == 0)
{
worksheet.get_Range("A1", "A1").EntireRow.Delete();
// неохота выносить присвоение за цикл, чтобы опять проверять условие
this.startRowBound = 1;
this.endRowBound = null;
}
return this;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
while (excel.WorksheetFunction.CountA(worksheet.get_Range("A1", "A1").EntireRow) == 0)
{
worksheet.get_Range("A1", "A1").EntireRow.Delete();
// неохота выносить присвоение за цикл, чтобы опять проверять условие
this.startRowBound = 1;
this.endRowBound = null;
}
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
uint row = GetFirstNonEmptyRowHeader();
Array values = (Array)worksheet.get_Range("A" + row.ToString()).EntireRow.Cells.Value;
foreach (string oldName in OldName)
{
int column = GetColumnIndex(values, oldName);
if (column != 0)
{
Range cell = worksheet.Cells[row, column];
cell.Value = NewName;
}
}
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
uint row = GetFirstNonEmptyRowHeader();
Array values = (Array)worksheet.get_Range("A" + row.ToString()).EntireRow.Cells.Value;
foreach (string oldName in OldName)
{
int column = GetColumnIndex(values, oldName);
if (column != 0)
{
Range cell = worksheet.Cells[row, column];
cell.Value = NewName;
}
}
return this;
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
uint headerRowIndex = GetStartRowBound();
ushort firstColumnIndex = GetFirstNonEmptyColumn();
// есть пустые строки до начала данных
if (headerRowIndex != 1)
{
headerRowIndex--;
if (this.startRowBound != null)
{
startRowBound--;
}
}
else
{
Range headerCell = worksheet.Cells[1, 1];
headerCell.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown);
if (this.endRowBound != null)
{
this.endRowBound++;
}
}
int counter = 0;
for (ushort i = firstColumnIndex; i < firstColumnIndex + ColumnHeaders.Count; i++)
{
Range cell = worksheet.Cells[headerRowIndex, i];
cell.Value = ColumnHeaders[counter++];
}
return(this);
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
uint headerRowIndex = GetStartRowBound();
uint lastRowIndex = GetEndRowBound(headerRowIndex);
uint columnIndex = GetIndexForNewColumn(headerRowIndex, lastRowIndex);
Range headerCell = worksheet.Cells[headerRowIndex, columnIndex];
headerCell.Value = this.ColumnHeaderName;
Range cellStart = worksheet.Cells[headerRowIndex + 1, columnIndex];
Range cellEnd = worksheet.Cells[lastRowIndex, columnIndex];
Range cells = worksheet.Range[cellStart, cellEnd];
double doubleStartValue = 0;
int intStartValue = 0;
int intStep = 0;
DateTime dateStartValue = DateTime.MinValue;
string localDataType = null;
if (!string.IsNullOrEmpty(dataType))
{
localDataType = dataType.ToLowerInvariant();
switch (localDataType)
{
case "double":
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
doubleStartValue = Double.Parse(Value, nfi);
break;
case "int":
intStartValue = Int32.Parse(Value, NumberStyles.Integer);
intStep = (int)step;
break;
case "datetime":
dateStartValue = DateTime.Parse(Value);
break;
default:
throw new ArgumentException("Неизвестный тип данных", Value);
break;
}
}
// если тип данных указан - значит будет изменение значения
if (!string.IsNullOrEmpty(this.dataType))
{
int counter = 0;
for (uint i = headerRowIndex + 1; i <= lastRowIndex; i++)
{
Range cell = worksheet.Cells[i, columnIndex];
switch (localDataType)
{
case "double":
cell.Value = doubleStartValue + step * counter++;
break;
case "int":
cell.Value = intStartValue + intStep * counter++;
break;
case "datetime":
cell.Value = IncrementDate(stepPattern, dateStartValue, counter++);
break;
default:
break;
}
}
}
else
cells.Value = this.Value;
return this;
}
protected void Init(TransformCommand another)
{
if (another != null)
{
this.endColumnBound = another.endColumnBound;
this.endRowBound = another.endRowBound;
this.excel = another.excel;
this.sheetNumber = another.sheetNumber;
this.startColumnBound = another.startColumnBound;
this.startRowBound = another.startRowBound;
this.workbook = another.workbook;
}
}
static void Main(string[] args)
{
//AddColumn add = new XmlSerializationResearch.AddColumn();
//XmlElementAttribute myElementAttribute = new XmlElementAttribute();
//myElementAttribute.ElementName = "Source";
//XmlAttributes myAttributes = new XmlAttributes();
//myAttributes.XmlElements.Add(myElementAttribute);
//XmlAttributeOverrides myOverrides = new XmlAttributeOverrides();
//myOverrides.Add(typeof(TestCommand), "Column", myAttributes);
//XmlSerializer serializer = new XmlSerializer(typeof(TestCommand), myOverrides);
//MemoryStream stream = new MemoryStream();
//serializer.Serialize(stream, add);
//stream.Position = 0;
//Console.WriteLine(new StreamReader(stream).ReadToEnd());
//string a = "<TestCommand><Source>D</Source></TestCommand>";
//TestCommand b = (TestCommand)serializer.Deserialize(new MemoryStream(Encoding.UTF8.GetBytes(a)));
//b.SetExcelApp(new Microsoft.Office.Interop.Excel.Application());
//b.SetFilePath(Path.Combine(System.Windows.Forms.Application.StartupPath, "Sheet.xls"));
//b.CastColumnIdentifier();
//b.Execute(null);
//string r = "<RemoveColumn><ColumnIndex>1</ColumnIndex><ColumnName>1</ColumnName></RemoveColumn>";
//RemoveColumn re = (RemoveColumn)serializer.Deserialize(new MemoryStream(Encoding.UTF8.GetBytes(r)));
//Console.WriteLine("Done");
//Console.ReadKey();
//return;
//string astr = "<A><i>3</i></A>";
//II a = (II)ser1.Deserialize(new MemoryStream(Encoding.UTF8.GetBytes(astr)));
//Console.WriteLine(a.getI());
#region Main
ScenarioReader reader = new ScenarioReader();
IList <ITransformCommand> commands = reader.LoadCommands(Path.Combine(System.Windows.Forms.Application.StartupPath, @"TestCommands\Commands.xml"));
if (commands.Count != 0)
{
commands[0].SetExcelApp(new Microsoft.Office.Interop.Excel.Application());
commands[0].SetFilePath(Path.Combine(System.Windows.Forms.Application.StartupPath, "Sheet.xls"));
}
TransformCommand prevCommand = null;
for (int i = 0; i < commands.Count; i++)
{
Console.WriteLine("{0}: Executing {1}", DateTime.Now, commands[i].GetType());
prevCommand = commands[i].Execute(prevCommand);
}
#endregion
//RenameHeader header = new RenameHeader();
//header.OldName = new List<string>();
//header.OldName.Add("FIO");
//header.OldName.Add("ФИО");
//header.NewName = "Column1";
//XmlSerializer serializer = new XmlSerializer(typeof(RenameHeader));
//MemoryStream stream = new MemoryStream();
//serializer.Serialize(stream, header);
//stream.Position = 0;
//Console.WriteLine(new StreamReader(stream).ReadToEnd());
////string serializedHeader = "<RenameHeader><OldName><Item>FIO</Item><Item>ФИО</Item></OldName><NewName>Column2</NewName></RenameHeader>";
//stream.Position = 0;
//string serializedHeader = new StreamReader(stream).ReadToEnd();
//MemoryStream streamToDeserialize = new MemoryStream(Encoding.UTF8.GetBytes(serializedHeader));
//ITransformCommand deserialized = (ITransformCommand)serializer.Deserialize(streamToDeserialize);
//deserialized.Excel = new Microsoft.Office.Interop.Excel.Application();
//deserialized.FilePath = Path.Combine(System.Windows.Forms.Application.StartupPath, "Sheet.xls");
//deserialized.SetExcelApp(new Microsoft.Office.Interop.Excel.Application());
//deserialized.SetFilePath(Path.Combine(System.Windows.Forms.Application.StartupPath, "Sheet.xls"));
//deserialized.Execute(null);
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Visible = true;
//// нужен полный путь к файлу
//Workbook workbook = excel.Workbooks.Open(Path.Combine(System.Windows.Forms.Application.StartupPath, "Sheet.xls"));
//Sheets sheets = workbook.Worksheets;
//Worksheet worksheet = (Worksheet)sheets.get_Item(1);
//Stopwatch stopwatch = new Stopwatch();
//stopwatch.Start();
//RemoveTopEmptyRows(excel, worksheet);
//RemoveLeftEmptyColumns(excel, worksheet);
//RenameHeader(excel, worksheet, "column5", "abababa");
//RemoveRow(worksheet, 5);
//RemoveRow(worksheet, 3);
////RemoveColumn(worksheet, 2);
////RemoveColumn(worksheet, "B");
//ushort firstRow = GetFirstNonEmptyRowHeader(excel, worksheet);
//ushort lastRow = GetLastNonEmptyRowForward(excel, worksheet, firstRow);
//Console.WriteLine(firstRow);
//Console.WriteLine(lastRow);
//Console.WriteLine(GetIndexForNewColumn(excel, worksheet, firstRow, lastRow));
//AddColumn(excel, worksheet, "newColumn1", "newValue");
//AddColumn(excel, worksheet, "newColumn2", DateTime.Now);
////AddColumn(excel, worksheet, "newColumn3", DateTime.Now, "-1d2h3m8s");
////AddColumn(excel, worksheet, "newColumn5", 1, 3);
////AddColumn(excel, worksheet, "newColumn6", 1.5, 3.5);
////AddColumn(excel, worksheet, "newColumn7", 1.5);
//string[] values = null;
//stopwatch.Stop();
//Console.WriteLine("Excel process time: " + stopwatch.Elapsed);
//Console.WriteLine("");
Console.WriteLine("Done.");
Console.ReadKey();
}
public TransformCommand Execute(TransformCommand previousCommand)
{
Init(previousCommand);
uint headerRowIndex = GetStartRowBound();
uint lastRowIndex = GetEndRowBound(headerRowIndex);
uint columnIndex = GetIndexForNewColumn(headerRowIndex, lastRowIndex);
Range headerCell = worksheet.Cells[headerRowIndex, columnIndex];
headerCell.Value = this.ColumnHeaderName;
Range cellStart = worksheet.Cells[headerRowIndex + 1, columnIndex];
Range cellEnd = worksheet.Cells[lastRowIndex, columnIndex];
Range cells = worksheet.Range[cellStart, cellEnd];
double doubleStartValue = 0;
int intStartValue = 0;
int intStep = 0;
DateTime dateStartValue = DateTime.MinValue;
string localDataType = null;
if (!string.IsNullOrEmpty(dataType))
{
localDataType = dataType.ToLowerInvariant();
switch (localDataType)
{
case "double":
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
doubleStartValue = Double.Parse(Value, nfi);
break;
case "int":
intStartValue = Int32.Parse(Value, NumberStyles.Integer);
intStep = (int)step;
break;
case "datetime":
dateStartValue = DateTime.Parse(Value);
break;
default:
throw new ArgumentException("Неизвестный тип данных", Value);
break;
}
}
// если тип данных указан - значит будет изменение значения
if (!string.IsNullOrEmpty(this.dataType))
{
int counter = 0;
for (uint i = headerRowIndex + 1; i <= lastRowIndex; i++)
{
Range cell = worksheet.Cells[i, columnIndex];
switch (localDataType)
{
case "double":
cell.Value = doubleStartValue + step * counter++;
break;
case "int":
cell.Value = intStartValue + intStep * counter++;
break;
case "datetime":
cell.Value = IncrementDate(stepPattern, dateStartValue, counter++);
break;
default:
break;
}
}
}
else
{
cells.Value = this.Value;
}
return(this);
}
