/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 复制列（在指定列右边复制指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 复制列（在指定WorkSheet指定列右边复制指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range1 = workSheet.get_Range(startCell, endCell);
range2 = workSheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetName">WorkSheet名称</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell)
{
try
{
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
}
}
if (sheet != null)
{
for (int i = sheetCount; i >= 1; i--)
{
range1 = sheet.get_Range(startCell, endCell);
range2 = sheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void ManageTemplatePart(RenderingContext renderingContext, ref int currentBindingContextItemId, ref int vOffset, int startPos, int endPos)
{
ExcelInterop.Worksheet worksheetFrom = partToRenderDefinition.DefinitionFirstCell.Worksheet;
ExcelInterop.Worksheet worksheetTo = currentRenderingTo.Worksheet;
int gap = endPos - startPos;
ExcelInterop.Range source = worksheetFrom.Cells[partToRenderDefinition.DefinitionFirstCell.Row + renderingContext.RowId, partToRenderDefinition.DefinitionFirstCell.Column + startPos];
source = source.Resize[1, gap];
ExcelInterop.Range workingRange = currentRenderingTo.Resize[1, gap];
source.Copy(workingRange);
int bindingContextItemsCount = renderingContext.ContextElement.BindingContextItems.Count;
for (int colId = startPos; colId < endPos; colId++)
{
IBindingContextItem item = partToRenderDefinition.DefinitionParts[renderingContext.RowId, colId] == null || bindingContextItemsCount <= currentBindingContextItemId
? null
: renderingContext.ContextElement.BindingContextItems[currentBindingContextItemId++];
if (item != null)
{
if (item is ExcelBindingSearchContextItem)
{
ExcelInterop.Range range = worksheetTo.Cells[currentRenderingTo.Row, currentRenderingTo.Column + colId - startPos];
((ExcelBindingSearchContextItem)item).SetRange(ref range);
range = null;
}
else if (item is IExcelControl)
{
ExcelInterop.Range range = worksheetTo.Cells[currentRenderingTo.Row, currentRenderingTo.Column + colId - startPos];
((IExcelControl)item).CreateControl(range);
range = null;
}
if (item.BindingDefinition != null)
{
if (item.BindingDefinition.IsEnum && !item.BindingDefinition.IsReadOnly)
{
ExcelInterop.Range range = worksheetTo.Cells[currentRenderingTo.Row, currentRenderingTo.Column + colId - startPos];
enumManager.CreateControl(item, ref range);
range = null;
}
if (item.BindingDefinition.IsMultiLine)
{
ExcelInterop.Range range = worksheetTo.Cells[currentRenderingTo.Row, currentRenderingTo.Column + colId - startPos];
ExcelInterop.Range localSource = source[1, 1 + colId - startPos];
multiLineManager.CreateControl(item, ref range, ref localSource, ref vOffset);
range = null;
}
if (item.BindingDefinition.OnAfterRendering != null)
{
ExcelInterop.Range range = worksheetTo.Cells[currentRenderingTo.Row, currentRenderingTo.Column + colId - startPos];
AddAfterRenderingAction(item.BindingDefinition, range);
range = null;
}
}
}
renderingContext.DataRow.Add(item);
}
source = null;
workingRange = null;
ExcelApplication.ReleaseComObject(worksheetFrom);
ExcelApplication.ReleaseComObject(worksheetTo);
worksheetFrom = null;
worksheetTo = null;
}
/// <summary>
/// Метод копирует выбранный диапазон ячеек на новое место
/// </summary>
/// <param name="start">Левая верхняя ячейка диапазона</param>
/// <param name="end">Правая нижняя ячейка диапазона</param>
/// <param name="destination">Ячейка нового местоположения</param>
public void CopyCells(object start, object end, object destination)
{
Excel.Range rangeDest = _xlWorkSheet.Range[destination, _misValue];
_range = _xlWorkSheet.Range[start, end];
_range.Copy(rangeDest);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
int x1 = 7;
int y1 = 1;
int x2 = 24;
int y2 = 15;
b = 20;
multiplier = 0;
DateTime date_start, date_end;
if (true)
{
bool flag = true;
if (flag_day)
{
DateTime dateTime = DateTime.Now;
date_start = dateTime.AddDays(int_old_day * (-1));
date_end = dateTime.AddDays(-1);
}
else
{
if (data_start_cal.SelectedDate != null && data_end_cal.SelectedDate != null)
{
date_start = data_start_cal.SelectedDate.Value;
date_end = data_end_cal.SelectedDate.Value;
}
else
{
MessageBox.Show("задайте начальную и конечную дату");
return;
}
}
Create_Excel_book();
wbSheet.Cells[4, 3] = " за период с " + date_start.ToShortDateString() + " " + date_start.ToShortTimeString() + " по " + date_end.ToShortDateString() + " " + date_end.ToShortTimeString();
Process(date_start);
multiplier = 1;
if (!System.DateTime.Equals(date_start, date_end))
{
date_start = date_start.AddDays(1);
while (flag)
{
if (System.DateTime.Equals(date_start, date_end))
{
flag = false;
//break;
}
Excel.Range rng_from = wbSheet.Range[wbSheet.Cells[x1, y1], wbSheet.Cells[x2, y2]];
Excel.Range rng_to = wbSheet.Range[wbSheet.Cells[x1 + b * multiplier, y1], wbSheet.Cells[x2 + b * multiplier, y2]];
rng_from.Copy(rng_to);
Process(date_start);
multiplier++;
date_start = date_start.AddDays(1);
}
}
}
}
public void CreateXlSheet(List <Team> Team)
{
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel._Worksheet dSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add());
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] = "TeamName";
oSheet.Cells[1, 2] = "TeamCleanSheets";
for (int i = 0; i < Team.Count; i++)
{
int row = i + 1;
oSheet.Cells[row, 1] = Team[i].TeamName;
oSheet.Cells[row, 2] = Team[i].TeamCleanSheets;
}
oXL.UserControl = false;
oWB.SaveAs(@"C:\Users\aditya.bhosle\source\repos\Data\Data\NewFolder1\TeamCleanSheets.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oXL.Visible = true;
string srcPath = (@"C:\Users\aditya.bhosle\Desktop\ISL\FTeamcleansheets.xls");
oWB = (excel._Workbook)(oXL.Workbooks.Open(srcPath));
oSheet = oWB.Worksheets.get_Item(1);
string destPath = (@"C:\Users\aditya.bhosle\source\repos\Data\Data\NewFolder1\TeamCleanSheets.xlsx");
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Open(destPath));
dSheet = oWB.Worksheets.Add();
excel.Range from = oSheet.Range["A:A,B:B"];
excel.Range torange = dSheet.Range["A1:B1"];
from.Copy(torange);
oXL.ActiveSheet.Range["D2:D1000"] = "=VLOOKUP(A2,Sheet1!A:B,1,False)";
oXL.ActiveSheet.Range["E2:E1000"] = "=VLOOKUP(A2,Sheet1!A:B,2,FALSE)";
oXL.ActiveSheet.Range["F2:F1000"] = "=EXACT(D:D,A:A)";
oXL.ActiveSheet.Range["G2:G1000"] = "=EXACT(E:E,B:B)";
oXL.UserControl = false;
oWB.SaveAs(@"C:\Users\aditya.bhosle\source\repos\Data\Data\NewFolder1\TeamCleanSheets.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oXL.Visible = true;
}
catch (Exception) { }
}
private static void 制单元格(Excel.Range range, Excel.Range range1)
{
range.Copy(Type.Missing);
range1.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
}
private void saveSpecification()
{
app = new Excel.Application();
book = app.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, templateSpecPath));
sheet = (Excel.Worksheet)book.Sheets[1];
//шапка
sheet.Cells[3, "BC"] = contract.ContractNumber.ToString();
sheet.Cells[4, "AX"] = contract.ConclusionDate.ToString(dateFormat);
//подвал
sheet.Cells[23, "C"] = contract.Provider.Name;
sheet.Cells[25, "C"] = contract.ProviderAgentRole;
sheet.Cells[25, "I"] = contract.ProviderAgentName;
sheet.Cells[23, "AO"] = contract.OrganizationDescription.Name;
sheet.Cells[25, "AO"] = contract.CustomerAgentRole;
sheet.Cells[25, "AU"] = contract.CustomerAgentName;
//..тело
List <ContractLine> lines = contract.getContractLines();
int extraStringsAmount = 0;
if (lines.Count > amountStringDefault)
{
extraStringsAmount = lines.Count - amountStringDefault;
}
int endStringInDoc = 19;
while (extraStringsAmount > 0)
{
Excel.Range excel_str = sheet.get_Range("A" + 12.ToString(), "BQ" + 15.ToString());
excel_str.Copy(Type.Missing);
Excel.Range r = (Excel.Range)sheet.Rows[endStringInDoc];
r.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
Excel.Range paste = sheet.get_Range("A" + endStringInDoc.ToString(), "BQ" + (endStringInDoc + 3).ToString());
paste.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
extraStringsAmount -= 4;
endStringInDoc += 4;
}
int startTableIndex = 11;
for (int i = 0; i < lines.Count; i++)
{
sheet.Cells[startTableIndex + i, "B"] = (i + 1).ToString();
sheet.Cells[startTableIndex + i, "E"] = lines[i].Resource.Name;
sheet.Cells[startTableIndex + i, "X"] = lines[i].Resource.Cipher;
sheet.Cells[startTableIndex + i, "AE"] = lines[i].Amount;
sheet.Cells[startTableIndex + i, "AJ"] = lines[i].Resource.Unit.Name;
sheet.Cells[startTableIndex + i, "AS"] = lines[i].Resource.Unit.Cipher;
sheet.Cells[startTableIndex + i, "AW"] = lines[i].UnitPrice.ToString();
sheet.Cells[startTableIndex + i, "BB"] = lines[i].TotalPrice.ToString();
sheet.Cells[startTableIndex + i, "BI"] = lines[i].DeliveryDate.ToString(dateFormat);
}
sheet.get_Range("A11", "BI" + (startTableIndex + lines.Count - 1).ToString()).
Rows.RowHeight = 30;
sheet.PageSetup.PrintArea = "";
//сохранение
FileInfo fileInfo = new FileInfo(Path.Combine(pathSave, saveSpecName));
if (fileInfo.Exists)
{
fileInfo.Delete();
}
book.SaveAs(Path.Combine(pathSave, saveSpecName), Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
book.Close();
app.Quit();
}
//Create the Excel file from dataset
public void Okuri(DataTable ds, string SOKOCD)
{
string saveFileName = "送り状兼受領書_" + SOKOCD + "_" + DateTime.Now.ToString("yyyyMMddHHmmss");
string templetFile = @"template\送り状兼受領書.xlsx";
SaveFileDialog saveDialog = new SaveFileDialog {
Filter = "Excel文件 |*.xlsx",
FileName = saveFileName,
RestoreDirectory = true
};
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
File.Copy(templetFile, saveFileName, true);
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(saveFileName);
Excel.Worksheet worksheet = workbook.Worksheets[1];
excel.Visible = false;
try
{
log.Info("EXEC BEGIN");
var distinctIds = ds.AsEnumerable()
.Select(s => new
{
area = s.Field <string>("AREANM"),
homen = s.Field <string>("HOMEN"),
//soko = s.Field<string>("SOKONM"),
//nukn = s.Field<string>("NUKNNM"),
})
.Distinct().ToList();
if (distinctIds.Count > 1)
{
int no = 0;
foreach (var a in distinctIds)
{
string expression;
DataRow[] foundRows;
expression = "AREANM = '" + a.area.ToString() + "' AND HOMEN = '" + a.homen.ToString() + "'";
foundRows = ds.Select(expression);
worksheet.Name = foundRows[0][11] + "_" + foundRows[0][10];
workbook.Worksheets[1].Name = workbook.Worksheets[1].Name.Split(' ')[0];
worksheet.Cells[1, "H"] = foundRows[0][11];
worksheet.Cells[1, "AD"] = foundRows[0][10];
worksheet.Cells[5, "B"] = foundRows[0][2];
worksheet.Cells[5, "H"] = foundRows[0][5];
worksheet.Cells[5, "N"] = foundRows[0][3];
worksheet.Cells[5, "Y"] = foundRows[0][1];
worksheet.Cells[5, "AS"] = foundRows[0][6];
worksheet.Cells[5, "AW"] = foundRows[0][7];
worksheet.Cells[5, "BC"] = foundRows[0][8];
worksheet.Cells[5, "BM"] = foundRows[0][9];
worksheet.Cells[5, "CD"] = foundRows[0][12];
worksheet.Cells[5, "CN"] = "サイン";
for (int i = 0; i < foundRows.Length - 1; i++)
{
Excel.Range RngToCopy = worksheet.get_Range("B5").EntireRow;
Excel.Range RngToInsert = worksheet.get_Range("B" + (i + 6)).EntireRow;
RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy());
worksheet.Cells[6 + i, "B"] = foundRows[i + 1][2];
worksheet.Cells[6 + i, "H"] = foundRows[i + 1][5];
worksheet.Cells[6 + i, "N"] = foundRows[i + 1][3];
worksheet.Cells[6 + i, "Y"] = foundRows[i + 1][1];
worksheet.Cells[6 + i, "AS"] = foundRows[i + 1][6];
worksheet.Cells[6 + i, "AW"] = foundRows[i + 1][7];
worksheet.Cells[6 + i, "BC"] = foundRows[i + 1][8];
worksheet.Cells[6 + i, "BM"] = foundRows[i + 1][9];
worksheet.Cells[6 + i, "CD"] = foundRows[i + 1][12];
worksheet.Cells[6 + i, "CN"] = "サイン";
}
no++;
if (no < distinctIds.Count)
{
worksheet.Copy(workbook.Worksheets[1], Type.Missing);
worksheet.get_Range("B5:B1000").EntireRow.ClearContents();
worksheet.get_Range("CN6:CN1000").EntireRow.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
}
}
else
{
foreach (var a in distinctIds)
{
string expression;
DataRow[] foundRows;
expression = "AREANM = '" + a.area.ToString() + "' AND HOMEN = '" + a.homen.ToString() + "'";
foundRows = ds.Select(expression);
worksheet.Name = foundRows[0][11] + "_" + foundRows[0][10];
workbook.Worksheets[1].Name = workbook.Worksheets[1].Name.Split(' ')[0];
worksheet.Cells[1, "H"] = foundRows[0][11];
worksheet.Cells[1, "AD"] = foundRows[0][10];
worksheet.Cells[5, "B"] = foundRows[0][2];
worksheet.Cells[5, "H"] = foundRows[0][5];
worksheet.Cells[5, "N"] = foundRows[0][3];
worksheet.Cells[5, "Y"] = foundRows[0][1];
worksheet.Cells[5, "AS"] = foundRows[0][6];
worksheet.Cells[5, "AW"] = foundRows[0][7];
worksheet.Cells[5, "BC"] = foundRows[0][8];
worksheet.Cells[5, "BM"] = foundRows[0][9];
worksheet.Cells[5, "CD"] = foundRows[0][12];
worksheet.Cells[5, "CN"] = "サイン";
for (int i = 1; i < foundRows.Length; i++)
{
Excel.Range RngToCopy = worksheet.get_Range("B5").EntireRow;
Excel.Range RngToInsert = worksheet.get_Range("B" + (i + 5)).EntireRow;
RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy());
worksheet.Cells[5 + i, "B"] = foundRows[i][2];
worksheet.Cells[5 + i, "H"] = foundRows[i][5];
worksheet.Cells[5 + i, "N"] = foundRows[i][3];
worksheet.Cells[5 + i, "Y"] = foundRows[i][1];
worksheet.Cells[5 + i, "AS"] = foundRows[i][6];
worksheet.Cells[5 + i, "AW"] = foundRows[i][7];
worksheet.Cells[5 + i, "BC"] = foundRows[i][8];
worksheet.Cells[5 + i, "BM"] = foundRows[i][9];
worksheet.Cells[5 + i, "CD"] = foundRows[i][12];
worksheet.Cells[5 + i, "CN"] = "サイン";
}
}
}
}
catch (Exception e)
{
log.Error("ERROR" + e.Message);
}
finally
{
excel.DisplayAlerts = false;
Application.DoEvents();
workbook.Save();
excel.Quit();
Marshal.FinalReleaseComObject(excel);
log.Info("EXEC END");
}
}
else
{
MessageBox.Show("処理を中止しました。");
}
}
private static void dieAltenPositionenUebernehmen(Excel.Worksheet excelSheet, string dieVorlage)
{
int anfangVorlage;
int endeTabelle;
int endeZielTabelle;
int endeVorlageAlles;
int anfangZiel;
int endeZielAlles;
int anzahlZuKopieren;
try
{
vorlageWorkbook = excelApp.Workbooks.Open(dieVorlage,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
int anzahlBlaetter = vorlageWorkbook.Sheets.Count;
if (anzahlBlaetter > 0)
{
// das Excelblatt
vorlageSheet = (Excel.Worksheet)vorlageWorkbook.Sheets[1];
Excel.Range vorlageZeilen = vorlageSheet.UsedRange;
anfangVorlage = getAnfangDerTabelle(vorlageZeilen);
endeTabelle = getEndeDerTabelle(vorlageZeilen);
endeVorlageAlles = vorlageZeilen.Rows.Count;
anzahlZuKopieren = endeVorlageAlles - anfangVorlage;
Excel.Range zielZeilen = excelSheet.UsedRange;
anfangZiel = getAnfangDerTabelle(zielZeilen);
endeZielTabelle = anfangZiel + (endeTabelle - anfangVorlage);
endeZielAlles = anfangZiel + anzahlZuKopieren;
// Range Tabelle
string inserttext = "A" + anfangVorlage.ToString() + ":F" + endeTabelle.ToString();
Excel.Range TabellezuKopieren = vorlageSheet.get_Range(inserttext);
string copytext = "A" + anfangZiel.ToString() + ":F" + endeZielTabelle.ToString();
Excel.Range hierreinTabelle = excelSheet.get_Range(inserttext);
TabellezuKopieren.Copy(Type.Missing);
hierreinTabelle.PasteSpecial(Excel.XlPasteType.xlPasteAll,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false);
// danach
string danachtext = "A" + (endeTabelle + 1).ToString() + ":F" + (endeVorlageAlles + 1).ToString();
Excel.Range danachZuKopieren = vorlageSheet.get_Range(inserttext);
string copyrest = "A" + (endeZielTabelle + 1).ToString() + "F:" + (endeZielAlles + 1).ToString();
Excel.Range hierreinRest = excelSheet.get_Range(inserttext);
danachZuKopieren.Copy(Type.Missing);
hierreinRest.PasteSpecial(Excel.XlPasteType.xlPasteAll,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false);
}
// alles beenden und freigeben
vorlageWorkbook.Close(false, dieVorlage, null);
Marshal.ReleaseComObject(vorlageWorkbook);
}
catch (Exception ex)
{
Helper.LogHelper.WriteDebugLog(ex.ToString());
}
}
static void Main(string[] args)
{
string srcDir = Path.GetFullPath(args[0]);
string destDir = Path.GetFullPath(args[1]);
string templatePath = Path.GetFullPath(args[2]);
string templateType = args[3];
string fromSheet = null;
string toSheet = @"ENTRY";
string[] ranges = null;
if (templateType == "VISIT")
{
Array.Resize(ref ranges, 8);
ranges[0] = @"F4:I6";
ranges[1] = @"P4:S6";
ranges[2] = @"Z4:AC6";
ranges[3] = @"H10:AE16";
ranges[4] = @"H20:M20";
ranges[5] = @"Q20:V20";
ranges[6] = @"Z20:AE20";
ranges[7] = @"D22:AE24";
}
else if (templateType == "PRECALIBRATION")
{
Array.Resize(ref ranges, 27);
ranges[0] = @"O6";
ranges[1] = @"D8";
ranges[2] = @"H8";
ranges[3] = @"P8";
ranges[4] = @"AA8:AA9";
ranges[5] = @"H9";
ranges[6] = @"F10";
ranges[7] = @"A17:A18";
ranges[11] = @"F14:F18";
ranges[12] = @"K14:K18";
ranges[13] = @"V13:V18";
ranges[14] = @"AA13:AA18";
ranges[15] = @"J23:J36";
ranges[16] = @"J38";
ranges[17] = @"M23:M36";
ranges[18] = @"P25:P38";
ranges[19] = @"X23:X30";
ranges[20] = @"AB31";
ranges[21] = @"X32:X34";
ranges[22] = @"AC35:AC36";
ranges[23] = @"X38";
ranges[24] = @"K41:K44";
ranges[25] = @"V42:V44";
ranges[26] = @"X41";
}
else if (templateType == "POSTCHECK")
{
Array.Resize(ref ranges, 24);
ranges[0] = @"O6";
ranges[1] = @"D8";
ranges[2] = @"H8";
ranges[3] = @"P8";
ranges[4] = @"AA8:AA9";
ranges[5] = @"K9";
ranges[6] = @"J10";
ranges[7] = @"O10";
ranges[8] = @"K11";
ranges[9] = @"S11";
ranges[10] = @"A18:A19";
ranges[11] = @"F15:F19";
ranges[12] = @"K15:K19";
ranges[13] = @"V14:V19";
ranges[14] = @"AA14:AA19";
ranges[15] = @"I23";
ranges[16] = @"T23";
ranges[17] = @"AA23";
ranges[18] = @"J26:J40";
ranges[19] = @"M26:M40";
ranges[20] = @"X35:X37";
ranges[21] = @"AC38:AC39";
ranges[22] = @"X40";
ranges[23] = @"A43";
}
// get list of files from source directory
string[] fileNames = Directory.GetFiles(srcDir, "*.xlsx",
SearchOption.TopDirectoryOnly).Select(Path.GetFileName).ToArray();
Excel.Application excelApplication = new Excel.Application();
excelApplication.Application.DisplayAlerts = false;
try
{
foreach (string fileName in fileNames)
{
Console.WriteLine(String.Join(" ", "Copying", fileName));
// open source workbook
Excel.Workbook srcworkBook = excelApplication.Workbooks.Open(Path.Combine(srcDir, fileName));
// get sheet names
Excel.Sheets srcSheets = srcworkBook.Sheets;
ArrayList srcSheetNames = new ArrayList();
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in srcSheets)
{
srcSheetNames.Add(sheet.Name);
}
if (srcSheetNames.Contains("VISIT"))
{
fromSheet = @"VISIT";
}
else if (srcSheetNames.Contains("ENTRY"))
{
fromSheet = @"ENTRY";
}
else if (srcSheetNames.Contains("PRECHECK"))
{
fromSheet = @"PRECHECK";
}
else if (srcSheetNames.Contains("POSTCHECK"))
{
fromSheet = @"POSTCHECK";
}
else if (srcSheetNames.Contains("Sheet1"))
{
fromSheet = @"Sheet1";
}
else
{
Console.WriteLine("Could not detect input sheet");
}
Excel.Worksheet srcworkSheet = srcworkBook.Worksheets[fromSheet];
// open template
Excel.Workbook destworkBook = excelApplication.Workbooks.Open(templatePath, 0, false);
Excel.Worksheet destworkSheet = destworkBook.Worksheets[toSheet];
// copy ranges
foreach (string range in ranges)
{
Excel.Range from = srcworkSheet.Range[range];
Excel.Range to = destworkSheet.Range[range];
try
{
//from.Copy(to);
from.Copy();
to.PasteSpecial(Excel.XlPasteType.xlPasteValues);
}
catch
{
Console.WriteLine(String.Join(" ", " Could not copy range", range));
}
}
// save template to new workbook
srcworkBook.Close();
destworkBook.SaveAs(Path.Combine(destDir, fileName));
destworkBook.Close();
Console.WriteLine(String.Join(" ", "Sucessfully copied", fileName));
}
}
catch {
Console.WriteLine("An error occurred.");
}
finally
{
excelApplication.Quit();
}
}
/// <summary>
/// 复制行（在指定WorkSheet指定行下面复制指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// ћетод копирует выбранный диапазон ¤чеек на новое место
/// </summary>
/// <param name="start">Ћева¤ верхн¤¤ ¤чейка диапазона</param>
/// <param name="end">ѕрава¤ нижн¤¤ ¤чейка диапазона</param>
/// <param name="destination">ячейка нового местоположени¤</param>
public void copyCells(object start, object end, object destination)
{
Excel.Range rangeDest = xlWorkSheet.get_Range(destination, misValue);
range = xlWorkSheet.get_Range(start, end);
range.Copy(rangeDest);
}
private void button1_Click(object sender, EventArgs e)
{
textBox1.AppendText("Start decoupage");
string pathnotapme = @"D:\ptw\notepme";
//pathstylerfinal = @textBox12.Text + "\\changeStyle\\divi\\final";
string openfilex = @"D:\ptw\Histo.xlsx";
////////////////open excel///////////////////////////////////////
Thread.Sleep(3000);
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Workbook xlWorkBookx1;
Excel.Workbook xlWorkBooknewx1;
object misValue = System.Reflection.Missing.Value;
//////////creat modele histox.xls pour fichier diviser////////////////////////////////
Excel.Application xlAppRef;
Excel.Workbook xlWorkBookRef;
xlAppRef = new Excel.ApplicationClass();
xlAppRef.Visible = false;; xlAppRef.DisplayAlerts = false; xlAppRef.ScreenUpdating = false;
xlAppRef.DisplayAlerts = false;
xlWorkBookRef = xlAppRef.Workbooks.Open(openfilex, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//xlWorkBookRef = xlAppRef.Workbooks.Open(openfilex, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet xlWorkSheetRef = (Excel.Worksheet)xlWorkBookRef.Worksheets.get_Item("Historique");
Excel.Range rangeRefall = xlWorkSheetRef.get_Range("A1", "W" + getRowsLimit(xlWorkSheetRef));
object[,] valuess = (object[, ])rangeRefall.Value2;
//bug : le seul moyen pour supprimer la dernière colonne est de chnager la largeur de toutes les colonnes (on ne sait pas pourquoi) !!!
// xlWorkSheetRef.Cells.ColumnWidth = 20;
int rowcount = rangeRefall.Rows.Count;
int colcount = rangeRefall.Columns.Count;
Excel.Range rangeRef = xlWorkSheetRef.get_Range("A" + getRowsLimit(xlWorkSheetRef), "W" + getRowsLimit(xlWorkSheetRef));
rangeRef.EntireRow.Copy(misValue);
rangeRef.EntireRow.PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, misValue, misValue);
Excel.Range rangeRefdel = xlWorkSheetRef.UsedRange.get_Range("X1", xlWorkSheetRef.Cells[1, xlWorkSheetRef.UsedRange.Columns.Count - 1]) as Excel.Range;
rangeRefdel.EntireColumn.ClearContents();
rangeRefdel.EntireColumn.ClearFormats();
rangeRefdel.EntireColumn.Clear();
try
{
rangeRefdel = xlWorkSheetRef.UsedRange.get_Range("A" + (1 + getRowsLimit(xlWorkSheetRef)), "A" + xlWorkSheetRef.UsedRange.Rows.Count) as Excel.Range;
}
catch (Exception rx)
{
textBox1.AppendText(rx.ToString());
}
rangeRefdel.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
rangeRefdel = xlWorkSheetRef.UsedRange.get_Range("A1", "W" + (getRowsLimit(xlWorkSheetRef) - 1)) as Excel.Range;
rangeRefdel.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
Excel.Range rangeA1 = xlWorkSheetRef.Cells[1, 1] as Excel.Range;
rangeA1.Activate();
xlWorkSheetRef.SaveAs(@"D:\ptw\Histo.xlsx", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkBookRef.Close(true, misValue, misValue);
xlAppRef.Quit();
//////////////////////////////////////////////////////////////////////////////////
Thread.Sleep(3000);
xlApp = new Excel.ApplicationClass();
xlApp.Visible = false; xlApp.DisplayAlerts = false; xlApp.ScreenUpdating = false;
xlApp.DisplayAlerts = false;
xlApp.Application.DisplayAlerts = false;
//MessageBox.Show(openfilex);//D:\ptw\Histo.xls
string remplacehisto8 = "[" + openfilex.Substring(7, 9) + "]";
//xlWorkBook = xlApp.Workbooks.Open(openfilex, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
xlWorkBook = xlApp.Workbooks.Open(openfilex, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique");
Excel.Range range = xlWorkSheet.get_Range("A1", "AB2062");
object[,] values = (object[, ])range.Value2;
int rCnt = 0;
int rowx = xlWorkSheet.get_Range("A1", "AB2062").Rows.Count;
int cCnt = 0;
int col = 0;
int col3000 = 0;
int col4000 = 0;
int col5000 = 0;
int col8000 = 0;
int col83000 = 0;
rCnt = xlWorkSheet.get_Range("A1", "AB2062").Rows.Count;
for (cCnt = 1; cCnt <= xlWorkSheet.get_Range("A1", "AB2062").Columns.Count - 1; cCnt++)
{
string valuecellabs = Convert.ToString(values[rCnt, cCnt]);
if (Regex.Equals(valuecellabs, "3000"))
{
col3000 = cCnt;
}
if (Regex.Equals(valuecellabs, "4000"))
{
col4000 = cCnt;
}
if (Regex.Equals(valuecellabs, "5000"))
{
col5000 = cCnt;
}
if (Regex.Equals(valuecellabs, "8000"))
{
col8000 = cCnt;
}
if (Regex.Equals(valuecellabs, "10000"))
{
col = cCnt;
}
if (Regex.Equals(valuecellabs, "83000"))
{
col83000 = cCnt;
break;
}
}
int fileflag = 0;
for (int row = 25; row <= 2061; row++)
{
string value = Convert.ToString(values[row, col]);
if (Regex.Equals(value, "-1"))
{
textBox1.AppendText("Row number:" + row);
Thread.Sleep(3000);
xlWorkBookx1 = xlApp.Workbooks.Open(@"D:\ptw\Histo.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
// xlWorkBookx1 = xlApp.Workbooks.Open( @textBox12.Text + "\\Histox.xlsx", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet xlWorkSheetx1 = (Excel.Worksheet)xlWorkBookx1.Worksheets.get_Item("Historique");
string[] namestable = { "S-ACT.xlsx", "S-PAS.xlsx", "S-CR.xlsx", "S-ANN3.xlsx", "S-ANN4.xlsx", "S-ANN5.xlsx" };
string divisavenom = pathnotapme + "\\" + namestable[fileflag];
System.IO.Directory.CreateDirectory(pathnotapme);//////////////cree repertoire
xlWorkSheetx1.SaveAs(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkBookx1.Close(true, misValue, misValue);
////////////Grande titre "-1"/////////////////////////////////////////////////////////////////
if (Regex.Equals(Convert.ToString(values[25, col]), "-1"))
{
Excel.Range rangegtitre = xlWorkSheet.Cells[25, col] as Excel.Range;
Excel.Range rangePastegtitre = xlWorkSheet.UsedRange.Cells[24, 1] as Excel.Range;
rangegtitre.EntireRow.Cut(rangePastegtitre.EntireRow);
Excel.Range rangegtitreblank = xlWorkSheet.Cells[25, col] as Excel.Range;
rangegtitreblank.EntireRow.Delete(misValue);
row--;// point important, pour garder l'ordre de ligne ne change pas
}
////////////////////insertion///////////////////////////////////////////////////////////////////
Excel.Range rangeDelx = xlWorkSheet.Cells[row, col] as Excel.Range;
Excel.Range rangediviser = xlWorkSheet.UsedRange.get_Range("A1", xlWorkSheet.Cells[row - 1, col - 1]) as Excel.Range;
Excel.Range rangedelete = xlWorkSheet.UsedRange.get_Range("A25", xlWorkSheet.Cells[row - 1, col]) as Excel.Range;
rangediviser.EntireRow.Select();
rangediviser.EntireRow.Copy(misValue);
//MessageBox.Show(row.ToString());
xlWorkBooknewx1 = xlApp.Workbooks.Open(divisavenom, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//xlWorkBooknewx1 = xlApp.Workbooks.Open(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = false;
xlApp.Application.DisplayAlerts = false;
Excel.Worksheet xlWorkSheetnewx1 = (Excel.Worksheet)xlWorkBooknewx1.Worksheets.get_Item("Historique");
/*Jintao: clearcontents for the worksheet
* Excel.Range erals = xlWorkSheetnewx1.UsedRange;
* erals.ClearContents();*/
//xlWorkBooknewx1.set_Colors(misValue, xlWorkBook.get_Colors(misValue));
Excel.Range rangenewx1 = xlWorkSheetnewx1.Cells[1, 1] as Excel.Range;
rangenewx1.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, misValue);
xlWorkSheetnewx1.SaveAs(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
//modifier lien pour effacer cross file reference!!!!!!!!!!!!!!2003-2010
xlWorkBooknewx1.ChangeLink(openfilex, divisavenom);
xlWorkBooknewx1.Close(true, misValue, misValue);
////////////////////replace formulaire contient ptw/histo8.xls///////////////////
Excel.Workbook xlWorkBookremplace = xlApp.Workbooks.Open(divisavenom, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Excel.Workbook xlWorkBookremplace = xlApp.Workbooks.Open(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = false;
xlApp.Application.DisplayAlerts = false;
Excel.Worksheet xlWorkSheetremplace = (Excel.Worksheet)xlWorkBookremplace.Worksheets.get_Item("Historique");
Excel.Range rangeremplace = xlWorkSheetremplace.get_Range("A1", "W" + getRowsLimit(xlWorkSheetremplace));
rangeremplace.Cells.Replace(remplacehisto8, "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, Type.Missing, false, false);//NB remplacehisto8 il faut ameliorer pour adapder tous les cas
////////delete col8000 "-2"//////////////////////////////////////////////////
object[,] values8000 = (object[, ])rangeremplace.Value2;
for (int rowdel = 1; rowdel <= rangeremplace.Rows.Count; rowdel++)
{
string valuedel = Convert.ToString(values8000[rowdel, col8000]);
if (Regex.Equals(valuedel, "-2"))
{
Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowdel, col8000] as Excel.Range;
rangeDely.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
rangeremplace = xlWorkSheetremplace.UsedRange;
values8000 = (object[, ])rangeremplace.Value2;
rowdel--;
}
}
///////////////row hide "-5"////////////////////////////////////////////////
for (int rowhide = 1; rowhide <= rangeremplace.Rows.Count; rowhide++)
{
string valuedel = Convert.ToString(values8000[rowhide, col8000]);
if (Regex.Equals(valuedel, "-5"))
{
Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowhide, col8000] as Excel.Range;
rangeDely.EntireRow.Hidden = true;
}
}
///////////////row supprimer "-6"////////////////////////////////////////////////
for (int rowhide = 1; rowhide <= rangeremplace.Rows.Count; rowhide++)
{
string valuedel = Convert.ToString(values8000[rowhide, col8000]);
if (Regex.Equals(valuedel, "-6"))
{
Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowhide, col8000] as Excel.Range;
rangeDely.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
rangeremplace = xlWorkSheetremplace.UsedRange;
values8000 = (object[, ])rangeremplace.Value2;
rowhide--;
}
}
///////////////Hide -1 pour col 83000/////////////////////////////////////////////
//for (int rowhide = 1; rowhide <= rangeremplace.Rows.Count; rowhide++)
//{
// string valuedel = Convert.ToString(values8000[rowhide, col83000]);
// if (Regex.Equals(valuedel, "-1"))
// {
// Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowhide, col83000] as Excel.Range;
// rangeDely.EntireRow.Hidden = true;
// }
//}
/////////////////////////////////////////////////////////////////////////////////
object[,] valuesNX = (object[, ])rangeremplace.Value2;
//string valueNX = Convert.ToString(valuesNX[row, col]);
for (int row3000 = 1; row3000 <= rangeremplace.Rows.Count; row3000++)
{
Excel.Range rangeprey = xlWorkSheetremplace.Cells[row3000, col3000] as Excel.Range;
if (Regex.Equals(Convert.ToString(valuesNX[row3000, col8000]), "-3"))
{
rangeprey.Locked = false;
rangeprey.FormulaHidden = false;
}
if (Regex.Equals(Convert.ToString(valuesNX[row3000, col8000]), "-4"))
{
rangeprey.Value2 = 0;
rangeprey.Locked = true;
rangeprey.FormulaHidden = true;
}
Excel.Range rangeDely = xlWorkSheetremplace.Cells[row3000, col3000] as Excel.Range;
if (rangeDely.Locked.ToString() != "True" && Convert.ToString(valuesNX[row3000, col8000]) != "-7")//-7 non zero
{
rangeDely.Value2 = 0;
}
}
for (int row4000 = 1; row4000 <= rangeremplace.Rows.Count; row4000++)
{
Excel.Range rangeprey = xlWorkSheetremplace.Cells[row4000, col4000] as Excel.Range;
if (Regex.Equals(Convert.ToString(valuesNX[row4000, col8000]), "-3"))
{
rangeprey.Locked = false;
rangeprey.FormulaHidden = false;
}
if (Regex.Equals(Convert.ToString(valuesNX[row4000, col8000]), "-4"))
{
rangeprey.Value2 = 0;
rangeprey.Locked = true;
rangeprey.FormulaHidden = true;
}
Excel.Range rangeDely = xlWorkSheetremplace.Cells[row4000, col4000] as Excel.Range;
if (rangeDely.Locked.ToString() != "True" && Convert.ToString(valuesNX[row4000, col8000]) != "-7")//-7 non zero
{
rangeDely.Value2 = 0;
}
}
for (int row5000 = 1; row5000 <= rangeremplace.Rows.Count; row5000++)
{
Excel.Range rangeprey = xlWorkSheetremplace.Cells[row5000, col5000] as Excel.Range;
if (Regex.Equals(Convert.ToString(valuesNX[row5000, col8000]), "-3"))
{
rangeprey.Locked = false;
rangeprey.FormulaHidden = false;
}
if (Regex.Equals(Convert.ToString(valuesNX[row5000, col8000]), "-4"))
{
rangeprey.Value2 = 0;
rangeprey.Locked = true;
rangeprey.FormulaHidden = true;
}
Excel.Range rangeDely = xlWorkSheetremplace.Cells[row5000, col5000] as Excel.Range;
if (rangeDely.Locked.ToString() != "True" && Convert.ToString(valuesNX[row5000, col8000]) != "-7")//-7 non zero
{
rangeDely.Value2 = 0;
}
}
int countr = xlWorkSheetremplace.UsedRange.Rows.Count;
int countc = xlWorkSheetremplace.UsedRange.Columns.Count;
object[,] valuesx = (object[, ])xlWorkSheetremplace.UsedRange.Value2;
Excel.Range rangeDeletex = xlWorkSheetremplace.UsedRange.get_Range("N1", xlWorkSheetremplace.Cells[1, xlWorkSheetremplace.UsedRange.Columns.Count]) as Excel.Range;
Excel.Range rangeDelete2 = xlWorkSheetremplace.get_Range(xlWorkSheetremplace.Cells[xlWorkSheetremplace.UsedRange.Rows.Count, 1], xlWorkSheetremplace.Cells[xlWorkSheetremplace.UsedRange.Rows.Count, 1]);
rangeDeletex.EntireColumn.Hidden = true;
rangeDelete2.EntireRow.Hidden = true;
////////////////////////////////////////////////////////////////////////////
xlApp.ActiveWindow.SplitRow = 0;
xlApp.ActiveWindow.SplitColumn = 0;
xlWorkBookremplace.Save();
xlWorkBookremplace.Close(true, misValue, misValue);
rangedelete.Copy(misValue);
rangedelete.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
range = xlWorkSheet.UsedRange;
values = (object[, ])range.Value2;
row = 25;//important remise le ligne commencer apres action delete 1:)25ligne
xlWorkSheet.Activate();
fileflag++;
}
}
xlApp.Quit();
MessageBox.Show("jobs done");
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}