public Export(string embedded) { string tmp = String.Empty; try { //получаем шаблон из прикладных ресурсов Stream template = GetResourceFileStream(embedded); //создаем временный файл tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx"); //сохраняем шаблон во временный файл using (var fileStream = File.Create(tmp)) { template.CopyTo(fileStream); } } catch (Exception ex) { MessageBox.Show(ex.Message); } // Создаем приложение и открываем в нём временный файл objApp = new Excel.Application(); objBook = objApp.Workbooks.Open(tmp); objBook.Activate(); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); }
public void readExcel() { string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Select the range of data containing the addresses and get the outer boundaries. rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; // Iterate through the data and concatenate the values into a comma-delimited string. for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++) { for (long colCounter = 1; colCounter <= lLastCol; colCounter++) { //Write the next value into the string. Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter]; string cellvalue = cell.Value.ToString(); //TODO: add your business logic for retrieve cell value } } }
public Export() { // Instantiate Excel and start a new workbook. objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add(Missing.Value); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); }
public ReportBuilder() { app = new Excel.Application(); appBooks = app.Workbooks; currentBook = appBooks.Add(Missing.Value); sheets = currentBook.Worksheets; currentSheet = (Excel._Worksheet)sheets.get_Item(1); range = currentSheet.get_Range("A1", Missing.Value); charts = currentSheet.ChartObjects(Type.Missing); chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); }
public string convertFiles(IList <string> filePaths) { m_fileCount = filePaths.Count; int fileIndex = 0; ArrayList sheet1ValueList = new ArrayList(); ArrayList sheet2ValueList = new ArrayList(); Cross_TopTag topTag; Cross_Data[] data; Cross_Paragraphs[][] paragraphs; Cross_Qas[][][] qas; Cross_Answers[][][][] answers; ETRI_TopTag EtopTag; object[,] sheet1ValueArray; object[,] sheet2ValueArray; int totalCountQas = 0; int totalCountParagraphs = 0; int sheet1TotalRowCount = 0; int sheet2TotalRowCount = 0; foreach (var item in filePaths) { m_path = item; var missing = Type.Missing; objApp = new Excel.Application(); objWorkbooks = objApp.Workbooks; int countParagraphs = 0; int countQas = 0; int currentRow = 0; bool excelOpen = false; try { if (m_currentConvertingMode == convertingMode.CJSONToCExcel) { #region JSON -> Excel 변환 // ** name1 영역 파싱 topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path)); // name2 영역 파싱 data = new Cross_Data[topTag.data.Count]; for (int i = 0; i < data.Length; i++) { data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString()); } // ** name3 영역 파싱 paragraphs = new Cross_Paragraphs[data.Length][]; for (int i = 0; i < data.Length; i++) { paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count]; for (int j = 0; j < data[i].paragraphs.Count; j++) { paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString()); countParagraphs++; totalCountParagraphs++; } } // ** name4 영역 파싱 qas = new Cross_Qas[data.Length][][]; for (int i = 0; i < data.Length; i++) { qas[i] = new Cross_Qas[paragraphs[i].Length][]; for (int j = 0; j < paragraphs[i].Length; j++) { qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count]; for (int k = 0; k < paragraphs[i][j].qas.Count; k++) { qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString()); countQas++; totalCountQas++; } } } // ** name5 영역 파싱 answers = new Cross_Answers[data.Length][][][]; for (int i = 0; i < data.Length; i++) { answers[i] = new Cross_Answers[paragraphs[i].Length][][]; for (int j = 0; j < paragraphs[i].Length; j++) { answers[i][j] = new Cross_Answers[qas[i][j].Length][]; for (int k = 0; k < qas[i][j].Length; k++) { answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count]; for (int m = 0; m < qas[i][j][k].answers.Count; m++) { answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString()); } } } } // ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정 sheet1RowCount = countQas; sheet2RowCount = countParagraphs; sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount]; sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount]; // ** sheet1ValueArray & sheet2ValueArray에 데이터 입력 // * paragraph 순번 & name1 영역 for (int row = 0; row < sheet2RowCount; row++) { sheet2ValueArray[row, 0] = sheet2TotalRowCount + 1; sheet2ValueArray[row, 1] = topTag.version; sheet2ValueArray[row, 2] = topTag.creator; sheet2ValueArray[row, 3] = topTag.progress; sheet2ValueArray[row, 4] = topTag.formatt; sheet2ValueArray[row, 5] = topTag.time; sheet2ValueArray[row, 6] = topTag.check; sheet2ValueArray[row, 7] = topTag.firstfile; sheet2ValueArray[row, 8] = topTag.secondfile; sheet2TotalRowCount++; } // * name2 & name3 영역 currentRow = 0; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { sheet2ValueArray[currentRow, 9] = data[d].title; sheet2ValueArray[currentRow, 10] = paragraphs[d][p].context; sheet2ValueArray[currentRow, 11] = paragraphs[d][p].context_en; sheet2ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged; currentRow++; } } // * name4 영역 currentRow = 0; int currentParaNum = 1; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { for (int q = 0; q < qas[d][p].Length; q++) { sheet1ValueArray[currentRow, 0] = sheet1TotalRowCount + 1; sheet1ValueArray[currentRow, 1] = qas[d][p][q].id; sheet1ValueArray[currentRow, 2] = qas[d][p][q].confuseQt1; sheet1ValueArray[currentRow, 3] = qas[d][p][q].confuseQf1; sheet1ValueArray[currentRow, 4] = qas[d][p][q].confuseSat1; sheet1ValueArray[currentRow, 5] = qas[d][p][q].confuseLat1; sheet1ValueArray[currentRow, 6] = qas[d][p][q].question; sheet1ValueArray[currentRow, 7] = qas[d][p][q].question_en; sheet1ValueArray[currentRow, 8] = qas[d][p][q].question_tagged1; sheet1ValueArray[currentRow, 9] = qas[d][p][q].questionType1; sheet1ValueArray[currentRow, 10] = qas[d][p][q].questionFocus1; sheet1ValueArray[currentRow, 11] = qas[d][p][q].questionSAT1; sheet1ValueArray[currentRow, 12] = qas[d][p][q].questionLAT1; sheet1ValueArray[currentRow, 13] = qas[d][p][q].confuseQt2; sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQf2; sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseSat2; sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseLat2; sheet1ValueArray[currentRow, 17] = qas[d][p][q].question_tagged2; // sheet1ValueArray[currentRow, 18] = qas[d][p][q].questionType2; // sheet1ValueArray[currentRow, 19] = qas[d][p][q].questionFocus2; // sheet1ValueArray[currentRow, 20] = qas[d][p][q].questionSAT2; // sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionLAT2; sheet1ValueArray[currentRow, 22] = qas[d][p][q].confuseQt3; sheet1ValueArray[currentRow, 23] = qas[d][p][q].confuseQf3; sheet1ValueArray[currentRow, 24] = qas[d][p][q].confuseSat3; sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseLat3; sheet1ValueArray[currentRow, 26] = qas[d][p][q].question_tagged3; sheet1ValueArray[currentRow, 27] = qas[d][p][q].questionType3; sheet1ValueArray[currentRow, 28] = qas[d][p][q].questionFocus3; sheet1ValueArray[currentRow, 29] = qas[d][p][q].questionSAT3; sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionLAT3; sheet1ValueArray[currentRow, 37] = currentParaNum; currentRow++; sheet1TotalRowCount++; } currentParaNum++; } } // * name5 영역 currentRow = 0; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { for (int q = 0; q < qas[d][p].Length; q++) { if (qas[d][p][q].answers.Count > 3) { return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + m_path); } int answerStartColNum = 31; for (int a = 0; a < answers[d][p][q].Length; a++) { sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text; sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en; sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged; sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn; sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start; sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end; answerStartColNum += 6; } currentRow++; } } } if ((++fileIndex) < m_fileCount) { sheet1ValueList.Add(sheet1ValueArray); sheet2ValueList.Add(sheet2ValueArray); continue; } sheet1ValueList.Add(sheet1ValueArray); sheet2ValueList.Add(sheet2ValueArray); //여러 sheetValueArray들을 하나로 통합 sheet1RowCount = totalCountQas; sheet2RowCount = totalCountParagraphs; sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount]; sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount]; int sheet1RowIndex = 0; int sheet2RowIndex = 0; int _sheet1RowCount; int _sheet2RowCount; for (int i = 0; i < sheet1ValueList.Count; i++) { object[,] tempSheet1Value = (object[, ])sheet1ValueList[i]; object[,] tempSheet2Value = (object[, ])sheet2ValueList[i]; _sheet1RowCount = (int)(tempSheet1Value.Length / sheet1ColCount); for (int j = 0; j < _sheet1RowCount; j++) { for (int k = 0; k < sheet1ColCount; k++) { sheet1ValueArray[sheet1RowIndex, k] = tempSheet1Value[j, k]; } sheet1RowIndex++; } _sheet2RowCount = (int)(tempSheet2Value.Length / sheet2ColCount); for (int j = 0; j < _sheet2RowCount; j++) { for (int k = 0; k < sheet2ColCount; k++) { sheet2ValueArray[sheet2RowIndex, k] = tempSheet2Value[j, k]; } sheet2RowIndex++; } } //엑셀파일에 writting excelOpen = true; objWorkbook = objWorkbooks.Add(missing); objWorksheets = objWorkbook.Worksheets; // * sheet2 부분 적용 objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1); objWorksheet.Name = "Paragraphs"; range = objWorksheet.get_Range("A1", "M1"); range.HorizontalAlignment = HCENTER; range.Interior.Color = Color.FromArgb(142, 169, 219); range.Value2 = sheet2ColHeader; Marshal.ReleaseComObject(range); Excel.Range c1 = objWorksheet.Cells[2, 1]; Excel.Range c2 = objWorksheet.Cells[sheet2RowCount + 1, sheet2ColCount]; range = objWorksheet.get_Range(c1, c2); range.Value = sheet2ValueArray; Marshal.FinalReleaseComObject(c1); Marshal.FinalReleaseComObject(c2); Marshal.FinalReleaseComObject(range); Marshal.ReleaseComObject(objWorksheet); // * sheet1 부분 적용 objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing); objWorksheet.Name = "Qas"; range = objWorksheet.get_Range("A1", "AL1"); range.HorizontalAlignment = HCENTER; range.Interior.Color = Color.FromArgb(142, 169, 219); range.Value2 = sheet1ColHeader; Marshal.ReleaseComObject(range); c1 = objWorksheet.Cells[2, 1]; c2 = objWorksheet.Cells[sheet1RowCount + 1, sheet1ColCount]; range = objWorksheet.get_Range(c1, c2); range.Value = sheet1ValueArray; Marshal.FinalReleaseComObject(c1); Marshal.FinalReleaseComObject(c2); Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); m_savePath = Path.ChangeExtension(m_path, "xlsx"); FileInfo fi = new FileInfo(m_savePath); if (fi.Exists) { fi.Delete(); } objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; excelOpen = false; #endregion } else { #region Excel -> JSON 변환 // ** Excel 파일 불러와서 object 이중배열에 데이터 입력 excelOpen = true; objWorkbook = objWorkbooks.Open(m_path); objWorksheets = objWorkbook.Worksheets; objWorksheet = (Excel.Worksheet)objWorksheets[1]; range = objWorksheet.UsedRange; sheet1ValueArray = (object[, ])range.get_Value(missing); Marshal.ReleaseComObject(range); Marshal.ReleaseComObject(objWorksheet); objWorksheet = (Excel.Worksheet)objWorksheets[2]; range = objWorksheet.UsedRange; sheet2ValueArray = (object[, ])range.get_Value(missing); Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; excelOpen = false; // ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력 // * topTag 객체 데이터 입력 EtopTag = new ETRI_TopTag(); EtopTag.version = sheet2ValueArray[2, 2] == null ? "" : sheet2ValueArray[2, 2].ToString(); EtopTag.creator = sheet2ValueArray[2, 3] == null ? "" : sheet2ValueArray[2, 3].ToString(); EtopTag.data = new List <object>(); // * topTag 객체 내의 Data 객체 리스트 입력 IList <object> titleList = new List <object>(); for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++) { object tempTitle = sheet2ValueArray[r, 10]; if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음) { titleList.Add(tempTitle); } else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == "" { titleList.Add(tempTitle); } else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과) { continue; } ETRI_Data tempData = new ETRI_Data(); tempData.title = tempTitle == null ? "" : tempTitle.ToString(); tempData.paragraphs = new List <object>(); EtopTag.data.Add(tempData); } // * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력 int dataCount = 0; object currentTitle = sheet2ValueArray[2, 10]; List <ETRI_Data> tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList(); for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++) { ETRI_Paragraphs tempParagraphs = new ETRI_Paragraphs(); tempParagraphs.context = sheet2ValueArray[r, 11] == null ? "" : sheet2ValueArray[r, 11].ToString(); tempParagraphs.context_en = sheet2ValueArray[r, 12] == null ? "" : sheet2ValueArray[r, 12].ToString(); tempParagraphs.context_tagged = sheet2ValueArray[r, 13] == null ? "" : sheet2ValueArray[r, 13].ToString(); tempParagraphs.qas = new List <object>(); if (sheet2ValueArray[r, 10] == null || sheet2ValueArray[r, 7].ToString() == "") { if (r != 2) { dataCount++; } tempDataList[dataCount].paragraphs.Add(tempParagraphs); currentTitle = sheet2ValueArray[r, 10] == null ? "" : sheet2ValueArray[r, 7].ToString(); } else if (sheet2ValueArray[r, 10] == currentTitle) { tempDataList[dataCount].paragraphs.Add(tempParagraphs); } else { dataCount++; tempDataList[dataCount].paragraphs.Add(tempParagraphs); currentTitle = sheet2ValueArray[r, 10].ToString(); } } EtopTag.data = tempDataList.Cast <object>().ToList(); // * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력 dataCount = 0; int paragraphCount = 0; int currentParagraph = 1; tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList(); List <ETRI_Qas> tempQasList = new List <ETRI_Qas>(); for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++) { ETRI_Qas tempQas = new ETRI_Qas(); tempQas.id = sheet1ValueArray[r, 2] == null ? "" : sheet1ValueArray[r, 2].ToString(); tempQas.question = sheet1ValueArray[r, 7] == null ? "" : sheet1ValueArray[r, 7].ToString(); tempQas.question_en = sheet1ValueArray[r, 8] == null ? "" : sheet1ValueArray[r, 8].ToString(); tempQas.question_tagged = sheet1ValueArray[r, 27] == null ? "" : sheet1ValueArray[r, 27].ToString(); tempQas.questionType = sheet1ValueArray[r, 28] == null ? "" : sheet1ValueArray[r, 28].ToString(); tempQas.questionFocus = sheet1ValueArray[r, 29] == null ? "" : sheet1ValueArray[r, 29].ToString(); tempQas.questionSAT = sheet1ValueArray[r, 30] == null ? "" : sheet1ValueArray[r, 30].ToString(); tempQas.questionLAT = sheet1ValueArray[r, 31] == null ? "" : sheet1ValueArray[r, 31].ToString(); int ansStartColNum = 32; ETRI_Answers tempAnswers = new ETRI_Answers(); tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? "" : sheet1ValueArray[r, ansStartColNum].ToString(); tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? "" : sheet1ValueArray[r, ansStartColNum + 1].ToString(); tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? "" : sheet1ValueArray[r, ansStartColNum + 2].ToString(); tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? "" : sheet1ValueArray[r, ansStartColNum + 3].ToString(); tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]); tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]); List <ETRI_Answers> tempAnswersList = new List <ETRI_Answers>(); /* * // * topTag->Data->Paragraphs->Qas 객체 리스트 내의 Answers 객체 리스트 입력 * for (int i = 0; i < 3; i++) * { * int ansStartColNum = 32;//18 * if (sheet1ValueArray[r, ansStartColNum] == null) * { * break; // 정답의 text 공백이면 없음 처리 * } * * ETRI_Answers tempAnswers = new ETRI_Answers(); * tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? null : sheet1ValueArray[r, ansStartColNum].ToString(); * tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString(); * tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? null : sheet1ValueArray[r, ansStartColNum + 2].ToString(); * tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? null : sheet1ValueArray[r, ansStartColNum + 3].ToString(); * tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]); * tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]); * * * tempAnswersList.Add(tempAnswers); * }*/ tempAnswersList.Add(tempAnswers); tempQas.answers = tempAnswersList.Cast <object>().ToList(); tempQasList.Add(tempQas); currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 38]); //36 if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때 { if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 38])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면 { EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입 tempQasList = new List <ETRI_Qas>(); if (paragraphCount < EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리 { paragraphCount++; } else // 도달하고 난 후에는 이렇게 처리 { dataCount++; paragraphCount = 0; } } } if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때 { EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); } } // ** JSON 파일로 저장 m_savePath = Path.ChangeExtension(m_path, "json"); FileInfo fi = new FileInfo(m_savePath); if (fi.Exists) // 파일이 이미 존재하면 삭제 { fi.Delete(); } string saveJSONText; bool m_EtoJNullRemoveCheck = false; if (m_EtoJNullRemoveCheck) { saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거 } ); } else { saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Include // Null값 객체 포함 } ); } using (StreamWriter sw = new StreamWriter(m_savePath)) { sw.Write(saveJSONText); } #endregion } } catch (Exception e) { if (excelOpen) { Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; } return("예외처리 된 오류 발생.\r\n파일: " + m_path); } } return("모든 파일 변환 성공"); }
public static void ReportDestination(List <ClassResource.Destination> destinations, List <ClassResource.Request> requests, List <ClassResource.Company> companies) { Excel.Application excelapp = new Excel.Application() { Visible = true }; Excel.Range excelcells; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(Type.Missing); Excel.Sheets excelsheets = excelapp.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelcells = excelworksheet.get_Range("A1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "№"; excelcells = excelworksheet.get_Range("B1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Наименование товара"; excelcells = excelworksheet.get_Range("C1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Количество"; excelcells = excelworksheet.get_Range("D1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Фирма"; excelcells = excelworksheet.get_Range("E1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Контактное лицо"; excelcells = excelworksheet.get_Range("F1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Телефон"; excelcells = excelworksheet.get_Range("G1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Точный адрес"; int rowCount = 2; foreach (var destination in destinations) { excelcells = excelworksheet.get_Range("A" + rowCount.ToString(), Type.Missing); excelcells.Value2 = destination.Number; excelcells = excelworksheet.get_Range("B" + rowCount.ToString(), Type.Missing); excelcells.Value2 = requests.Find(x => x.ID == destination.IDRequest).ProductName; excelcells = excelworksheet.get_Range("C" + rowCount.ToString(), Type.Missing); excelcells.Value2 = requests.Find(x => x.ID == destination.IDRequest).Quantity; excelcells = excelworksheet.get_Range("D" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(y => y.ID == requests.Find(x => x.ID == destination.IDRequest).IDCompany).Name; excelcells = excelworksheet.get_Range("E" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(y => y.ID == requests.Find(x => x.ID == destination.IDRequest).IDCompany).NameСontactPerson; excelcells = excelworksheet.get_Range("F" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(y => y.ID == requests.Find(x => x.ID == destination.IDRequest).IDCompany).PhoneContactPerson; excelcells = excelworksheet.get_Range("G" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(y => y.ID == requests.Find(x => x.ID == destination.IDRequest).IDCompany).Address; rowCount++; } excelcells = (Excel.Range)excelworksheet.Columns["A:A", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["B:B", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["C:C", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["D:D", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["E:E", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["F:F", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["G:G", Type.Missing]; excelcells.Columns.AutoFit(); }
private void button1_Click(object sender, EventArgs e) { if (textBox1.Text.Trim().Length == 0) { MessageBox.Show("Введите логин из Оперы", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); textBox1.Focus(); } else if (textBox2.Text.Trim().Length == 0) { MessageBox.Show("Введите Фамилию И. О.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); textBox2.Focus(); } else if (textBox3.Text.Trim().Length == 0) { MessageBox.Show("Введите номер кассира", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); textBox3.Focus(); } else if (comboBox1.Text.Trim().Length == 0) { MessageBox.Show("Введите номер кассы", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); comboBox1.Focus(); } else { var Login = Path.Combine(Directory.GetCurrentDirectory(), "Login.xls"); Excel.Application xlLog = new Excel.Application(); xlLog.Visible = false; if (xlLog == null) { MessageBox.Show("Не удалось открыть Excel !", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else { Excel.Workbook xlLogworkbook = xlLog.Workbooks.Open(Login); Excel.Sheets xlLogsheets = xlLogworkbook.Worksheets; Excel.Worksheet xlLogins = (Excel.Worksheet)xlLogsheets.get_Item("Users"); int i = 1; string value = (string)(xlLogins.Cells[i, "A"] as Excel.Range).Value; while (value != null) { i++; value = (string)(xlLogins.Cells[i, "A"] as Excel.Range).Value; } xlLogins.Cells[i, "A"] = textBox1.Text; xlLogins.Cells[i, "B"] = textBox2.Text; xlLogins.Cells[i, "C"] = textBox3.Text; xlLogins.Cells[i, "D"] = comboBox1.Text; Properties.Settings.Default.Cashier_Opera = textBox1.Text; Properties.Settings.Default.Cashier_name = textBox2.Text; Properties.Settings.Default.Cashier_number = textBox3.Text; Properties.Settings.Default.Cashier = comboBox1.Text; Properties.Settings.Default.Save(); xlLogworkbook.Close(true); DialogResult = DialogResult.OK; } } }
//a method to load in requested excelsheets based on their path in the file system public void readExcelSheet(string sheetPath) { int newStudents; //holds the values read from the spreadsheet to parse to double newMaxScore; double newActualScore; int rowCount = 0; //needed to iterate through the spreadsheets objNode currNode; //needed to iterate through the linked list string[] delimitedFileName = sheetPath.Split('_'); //breaks the file name into pieces based on the '_' char string[] delimitedCourseObjName; //delimits the course objective name in the nodes Excel.Application excelApp; //open excelApp Excel.Workbook excelWorkbook; //open a new workbook Excel.Worksheet excelWorksheet; //open a new worksheet excelApp = new Excel.Application(); //create the new application Excel.Range range; //range variable //get the current file path String path = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName; //path to the workbook in the dataSheets folder string workbookPath = path + "\\bin\\dataSheets\\" + sheetPath; try { excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //holds the worksheet Excel.Sheets excelSheets = excelWorkbook.Worksheets; //read from this sheet string currentSheet = "Sheet1"; excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet); //find all valid cells range = excelWorksheet.UsedRange; try { //for each row in the spreadsheet for (rowCount = 1; rowCount <= range.Rows.Count; rowCount++) { currNode = objectiveList.getHead(); //set current node to the first node in the list while (currNode != null) //while there are objective nodes left { foreach (string courseObj in currNode.getCourseObj()) //for each course objective mapped to the super objective { delimitedCourseObjName = courseObj.Split('-'); //delimit the objective into course and objective name if (delimitedFileName[2] == delimitedCourseObjName[0] && //if the objective and course range.Cells[rowCount, 1].Value2.ToString().Trim() == delimitedCourseObjName[1]) //name match { int.TryParse(range.Cells[rowCount, 2].Value2.ToString(), out newStudents); //parse the values to double.TryParse(range.Cells[rowCount, 3].Value2.ToString(), out newMaxScore); //the node from the double.TryParse(range.Cells[rowCount, 4].Value2.ToString(), out newActualScore); //spreadsheet currNode.addStudents(newStudents); currNode.setTotalStudents(currNode.getTotalStudents() + newStudents); //add the new number of students to the total currNode.addAverage(newActualScore / newMaxScore); } } currNode = currNode.getNextObjNode(); //get the next node in the list } } //close excelWorkbook.Close(true, null, null); excelApp.Quit(); } catch (Exception ex) { consoleOutputTxB.Visible = true; consoleBxLB.Visible = true; consoleOutputTxB.AppendText("ERROR: Exception: " + ex.GetType() + "\n"); excelWorkbook.Close(true, null, null); excelApp.Quit(); } } catch (FileNotFoundException ex) { consoleOutputTxB.Visible = true; consoleBxLB.Visible = true; consoleOutputTxB.AppendText("ERROR: FILE NOT READ, Exception: " + ex.GetType() + "\n"); } }
public int Zakupki(string pathodb, string pathoffile) { string s = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; SqlDataAdapter da = new SqlDataAdapter("Select * from Preparat", s); // MyDBDataSet ds = new MyDBDataSet(); // da.Fill(ds, "Preparat"); int resuly = 0; Excel.Application excelapp = new Excel.Application(); Excel.Workbook file; file = excelapp.Workbooks.Open(pathoffile, 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); Excel.Sheets sheets = excelapp.Worksheets; Excel.Worksheet worksheet; worksheet = (Excel.Worksheet)sheets.get_Item(1); Excel.Range cell_1 = (Excel.Range)worksheet.Cells[1, 1]; int i = 1, j = 1; string a1 = Convert.ToString(cell_1.Value2); a1 = a1.ToLower(); DataBase bd = new DataBase(pathodb); Excel.Range cell_2; i = 3; cell_1 = (Excel.Range)worksheet.Cells[i, 1]; while (Convert.ToString(cell_1.Value2) != null) { i++; cell_1 = (Excel.Range)worksheet.Cells[i, 1]; } int rowend = i-1; bool datt = false; string date = ""; while (datt == false) { if (Convert.ToString((cell_2 = (Excel.Range)worksheet.Cells[i, 6]).Value2) != null) { datt = true; date = Convert.ToString((cell_2 = (Excel.Range)worksheet.Cells[i, 6]).Value2); } i++; } for (i = 3; i <= rowend; i++) { cell_2 = (Excel.Range)worksheet.Cells[i, 4]; string postavchik = Convert.ToString(cell_2.Value2); int Id_postavchik = FindPostav(postavchik); string name = Convert.ToString((cell_2 = (Excel.Range)worksheet.Cells[i,1]).Value2); int kolvo = Convert.ToInt32((cell_2 = (Excel.Range)worksheet.Cells[i, 3]).Value2); float price = Convert.ToDouble((cell_2 = (Excel.Range)worksheet.Cells[i, 2]).Value2); int id_prep = bd.FindIdPrep(Id_postavchik); bd.Update_Preparat(id_prep, Id_postavchik, name, price, kolvo); } file.Close(); excelapp.Quit(); return resuly; }
private void exportexcel(System.Data.DataTable dt, string filename) { Microsoft.Office.Interop.Excel._Workbook oWB; Microsoft.Office.Interop.Excel.Series oSeries; // Microsoft.Office.Interop.Excel.Range oResizeRange; Microsoft.Office.Interop.Excel._Chart oChart; //String sMsg; //int iNumQtrs; GC.Collect();//系统的垃圾回收 //string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls"; //Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename); Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true); ep.Visible = true; Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets; Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx"); ws.UsedRange.Select(); ws.UsedRange.Copy(System.Type.Missing); // wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing); int rowIndex = 1; int colIndex = 1; foreach (DataColumn col in dt.Columns) { ws.Cells[rowIndex, colIndex] = col.ColumnName; colIndex++; } for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++) { DataRow row = dt.Rows[drvIndex]; colIndex = 1; foreach (DataColumn col in dt.Columns) { ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString(); colIndex++; } } oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent; oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[30, 30]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value, XlRowCol.xlColumns, true, true, true, this.bandedGridView1.GroupPanelText, Missing.Value, Missing.Value, Missing.Value); oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1); oChart.PlotVisibleOnly = false; // oChart.HasDataTable = true; Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes( Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary); Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes( Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary); //ax.HasTitle = true; //ax.AxisTitle.Text = "Sales Figures"; ax.HasMajorGridlines = true; //string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls"; ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); }
private unsafe void CreateExcelItem() { // Создаем документ с 16 страницами excelapp = new Excel.Application(); //excelapp.Visible=true; excelapp.SheetsInNewWorkbook=1; Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing); String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" }; excelsheets = excelappworkbook.Worksheets; // определяем имена страницам и переходим на страницу excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1); excelworksheet.Name = SheetsName[0]; excelworksheet.Activate(); excelworksheet.Application.ActiveWindow.SplitColumn = 3; excelworksheet.Application.ActiveWindow.SplitRow = 2; excelworksheet.Application.ActiveWindow.FreezePanes = true; // заполнение Index (0.1.2.3...) excelcells = excelworksheet.get_Range("B3", Type.Missing); excelcells.Value2 = 0; excelcells = excelworksheet.get_Range("B4", Type.Missing); excelcells.Value2 = 1; excelcells = excelworksheet.get_Range("B3", "B4"); Excel.Range dest = excelworksheet.get_Range("B3", "B514"); excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault); // сворачиваем для увеличения скорости excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized; excelworksheet.Application.Visible = false; // оцентровываем первую строку excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing]; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; // зажирняем и оцентровываем вторую строку excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing]; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; // устанавливаем размер колонок excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing]; excelcells.ColumnWidth = 5; excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing]; excelcells.ColumnWidth = 5; excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing]; excelcells.ColumnWidth = 30; for (int j = 0; j < MyItemColumns.Length; j++) { excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing]; excelcells.ColumnWidth = MyItemColumns[j].Width; } // заполняем первую строку границами как называется не помню excelcells = excelworksheet.get_Range("C1", Type.Missing); excelcells.Value2 = "Char[30]"; excelcells.Activate(); for (int j = 0; j < MyItemColumns.Length; j++) { excelcells = excelapp.ActiveCell.get_Offset(0, 1); excelcells.Value2 = MyItemColumns[j].ColSize; excelcells.Activate(); } // заполняем вторую строку названиями excelcells = excelworksheet.get_Range("A2", Type.Missing); excelcells.Value2 = "Type"; excelcells = excelworksheet.get_Range("B2", Type.Missing); excelcells.Value2 = "Index"; excelcells = excelworksheet.get_Range("C2", Type.Missing); excelcells.Value2 = "Item Name"; excelcells.Activate(); for (int j = 0; j < MyItemColumns.Length; j++) { excelcells = excelapp.ActiveCell.get_Offset(0, 1); excelcells.Value2 = MyItemColumns[j].Name; excelcells.Activate(); } // обнуляем все ячейки кроме названия excelcells = excelworksheet.get_Range("D3", "AR514"); excelcells.Value2 = 0; // number format 12 232 232 для zen excelcells = excelworksheet.get_Range("AB3", "AB514"); excelcells.NumberFormat = "# ##0"; // копируем листы for (int i = 0; i < 15; i++) { excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1); excelworksheet.Copy(Type.Missing, excelworksheet); excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2); excelworksheet.Name = SheetsName[i + 1]; } // заполняем ячейки for (int i = 0; i < 16; i++) { // выделяем нужный лист excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1); excelworksheet.Activate(); excelcells = excelworksheet.get_Range("A3", "A514"); excelcells.Value2 = i; progressBar3.Value = i; // поехали по строкам for (int j = 0; j < 512; j++) { progressBar2.Value = j; if (Items[i,j].ItemName[0] != '\0') { excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3]; excelcells.Value2 = Items[i, j].ItemName; excelcells.Select(); } fixed (Int64* buff = Items[i, j].Numbers) { for (int k = 0; k < MyItemColumns.Length; k++) { if (buff != null && *(buff + k) != 0.0f) { excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4]; excelcells.Value2 = *(buff + k); } } } } } // показываем готовый файл excelapp.Visible = true; progressBar2.Value = 0; progressBar3.Value = 0; MessageBox.Show("All Done!"); }
/// <summary> /// Reads each from worksheet from C6 to D6 and stores it in a dataTable. Stop the Excel application after the data is read. /// </summary> public void ReadDataFromExcelToDataTable() { Excel.Range AllCells = null; Excel.Range lastCell = null; Excel.Range row = null; try { //start Excel StartExcel(); //open workbook TagListWorkbook = m_excelObj.Workbooks.Open(m_pathToExcelDocument, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false); //create datatable with two columns m_excelData = new DataTable("TableData"); m_excelData.Columns.Add("Tag Name", Type.GetType("System.String")); m_excelData.Columns.Add("Address", Type.GetType("System.String")); //Set Tag Name column as primary key m_excelData.Columns["Tag Name"].Unique = true; m_excelData.PrimaryKey = new DataColumn[] { m_excelData.Columns["Tag Name"] }; //Get the sheets sheets = TagListWorkbook.Worksheets; //total number of sheets int TotalSheets = sheets.Count; //instantiate string of worksheet array worksheetNames = new string[TotalSheets]; //Read columns c6 to d6 from all the sheets and add into Data table for (int i = 1; i <= TotalSheets; i++) { //get each sheet worksheet = (Excel.Worksheet)sheets.get_Item(i); //store sheet names to be used later for database setup worksheetNames[i - 1] = worksheet.Name; AllCells = worksheet.get_Range("C6", "D6"); //lastCell = null; //Excel.Range AllCells = worksheet.get_Range("C6", "D6"); //Excel.Range lastCell = null; //get the last cell for column c6, d6 lastCell = AllCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value); //read data starting from column C and row 6 and to D6 to the last cell of these columns for (int j = 6; j <= lastCell.Row; j++) { row = worksheet.get_Range("C" + j.ToString(), "D" + j.ToString()); Array strs = (System.Array)row.Cells.Value2; //convert values to array of strings string[] strsArray = ConvertToStringArray(strs); //add the array of strings to the datatable m_excelData m_excelData.Rows.Add(strsArray); //delete 'Blank' row DeleteBlankRowsInDataTable(); } } } catch (Exception ex) { throw ex; } finally { //Clean up any references GC.Collect(); GC.WaitForPendingFinalizers(); //clean up Excel objects Release(AllCells); Release(lastCell); Release(row); Release(worksheet); Release(sheets); //Close the workbook TagListWorkbook.Close(false, Type.Missing, Type.Missing); Release(TagListWorkbook); //m_excelObj.Application.Quit(); - m_excelObj.Quit(); Release(m_excelObj); m_excelObj = null; } }
public void setActiveSheet(int idx) { excelworksheet = (Excel.Worksheet)excelsheets.get_Item(idx); }
private void cmbJoblist_SelectedIndexChanged(object sender, EventArgs e) { Excel.Workbook excelWorkbook = null; rd = null; resDB = null; GOSTOP = false; btnCalc.Enabled = false; try { txtTpn.Text = ""; txtTpsn.Text = ""; txtPosn.Text = ""; string str = txtCurDir.Text + "\\" + cmbJoblist.SelectedItem.ToString(); if (!File.Exists(str)) { MessageBox.Show("File is not found, " + str, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } // Read File and display the file header. Excel.Application excelApp = new Excel.Application(); // Creates a new Excel Application excelApp.Visible = false; // Makes Excel visible to the user. // The following code opens an existing workbook string workbookPath = str; // Add your own path here try { excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } catch (Exception ex) { MessageBox.Show("File open Error: " + ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } // The following gets the Worksheets collection Excel.Sheets excelSheets = excelWorkbook.Worksheets; // The following gets Sheet1 for editing string currentSheet = "RawDB"; Excel.Worksheet excelWorksheet = null; try { excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("RawDB"); } catch { try { excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("Raw DB"); } catch { MessageBox.Show("Error: There is not the Sheet that is named 'Raw DB'", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); if (excelWorkbook != null) { excelWorkbook.Close(); } return; } } // The following gets cell A1 for editing int tpn, tpsn, posn, RawStart, ColStart; try { tpn = (int)excelWorksheet.Cells[1, 2].Value2; Common.TPN = tpn; txtTpn.Text = tpn.ToString(); tpsn = (int)excelWorksheet.Cells[2, 2].Value2; Common.TSPN = tpsn; txtTpsn.Text = tpsn.ToString(); posn = (int)excelWorksheet.Cells[3, 2].Value2; Common.POSN = posn; txtPosn.Text = posn.ToString(); RawStart = (int)excelWorksheet.Cells[4, 2].Value2; ColStart = (int)excelWorksheet.Cells[4, 3].Value2; txtStart.Text = "Row:" + RawStart.ToString() + ",Col:" + ColStart.ToString(); } catch (Exception ex) { MessageBox.Show("Data Read Error, please check the input data(TPN,TPSN,POSN,Start Position) in 'RAW DB' sheet.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); if (excelWorkbook != null) { excelWorkbook.Close(); } return; } // Read All Data and make a RawData Class rd = new RawData(posn, tpn, tpsn); int d = 0; for (int i = 0; i < posn; i++) { for (int j = 0; j < tpn; j++) { rd.Set(i, j, 0); } for (int j = 0; j < tpn; j++) { try { d = Convert.ToInt32(excelWorksheet.Cells[RawStart + i, ColStart + j].Value2); if (d > 0 && d <= tpn) { rd.Set(i, d - 1, 1); } } catch { d = 0; } if (d == 0) { break; } } if (i % 100 == 0) { Console.WriteLine("line: " + i.ToString()); } } excelWorkbook.Close(); excelWorkbook = null; //rd.printRawData(); btnCalc.Enabled = true; StatusProgress.Value = 0; statusLabel1.Text = ""; timer1.Enabled = false; } catch (Exception ex) { MessageBox.Show("Error: unhandled error was raised. system message:" + ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); if (excelWorkbook != null) { excelWorkbook.Close(); } } }
private bool read_file(out string msg) { msg = null; Excel._Application app = new Excel.Application(); try { // string FilePath = Path.Combine(inpath, FileName); Excel._Workbook book = app.Workbooks.Open(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Excel.Sheets sheets = book.Worksheets; Excel._Worksheet sheet; sheet = (Excel._Worksheet)sheets.get_Item(1); Excel.Range range; data = new List <STRowUpdateContract>(); int lastRow = sheet.UsedRange.Rows.Count; for (int i = 1; i <= lastRow; i++) { range = sheet.get_Range(string.Format("A{0}", i), Missing.Value); if (range.Value2 == null) { continue; } string s = Convert.ToString(range.Value2).Trim(); if (!is_d(s)) { continue; } else { STRowUpdateContract item = new STRowUpdateContract(); range = sheet.get_Range(string.Format("A{0}", i), Missing.Value); if (range.Value2 != null) { item.contrcode = Convert.ToInt32(range.Value2); } else { item.contrcode = -1; } range = sheet.get_Range(string.Format("B{0}", i), Missing.Value); if (range.Value2 != null) { item.mdmcode = Convert.ToString(range.Value2).Trim(); } else { item.mdmcode = null; } if (item.contrcode > 0 && !string.IsNullOrEmpty(item.mdmcode)) { data.Add(item); } } } app.Quit(); } catch (Exception ex) { msg = ex.Message; log.LogLine("File wasn't read."); return(false); } finally { app.Quit(); } return(true); }
/// <summary> /// Add a new worksheet /// </summary> /// <param name="name"></param> public void AddWorksheet(string name) { workbook.Worksheets.Add(missing, missing, missing, missing); Excel.Worksheet worksheetName = (Excel.Worksheet)sheets.get_Item(1); worksheetName.Name = name; }
private void button1_Click(object sender, EventArgs e) { try { // Instantiate Excel and start a new workbook. ArrayList exInst = new ArrayList(); //List of existing Excel Process[] P_CESSES = Process.GetProcessesByName("EXCEL"); for (int p_count = 0; p_count < P_CESSES.Length; p_count++) { exInst.Add(P_CESSES[p_count].Id); } objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Open("Y:\\Mechanical\\Inventor\\iLogic_Plug_Ins\\iLogic_Drawings_Plug_Ins\\TestXL.xlsx"); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); objSheet.Cells[2, 1] = "Heyy"; Boolean bEndFound = false; int n = 0; String tempString; int tempCount = 0; if (cats == null) { cats = new Dictionary <string, int>(); } else { cats.Clear(); } ComboBox _cbCats = cbCats; while (!bEndFound) { objRange = ((Excel.Range)objSheet.Cells[3 + n, 4]); tempString = objRange.Text; if (tempString.Equals("")) { bEndFound = true; } else { objRange = ((Excel.Range)objSheet.Cells[3 + n, 5]); tempCount = Convert.ToInt32(objRange.Value2); cats.Add(tempString, tempCount); n++; _cbCats.Items.Add(tempString); } } objBook.Save(); objBook.Close(0); Boolean bFound = false; P_CESSES = Process.GetProcessesByName("EXCEL"); for (int p_count = 0; p_count < P_CESSES.Length; p_count++) { bFound = false; foreach (int s in exInst) { if (s.Equals(P_CESSES[p_count].Id)) { bFound = true; } } if (!bFound) { P_CESSES[p_count].Kill(); } } while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objApp) != 0) { } while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBooks) != 0) { } while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBook) != 0) { } while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheets) != 0) { } while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheet) != 0) { } objApp = null; objBooks = null; objBook = null; objSheets = null; objSheet = null; GC.Collect(); GC.WaitForPendingFinalizers(); } catch (Exception myError) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, myError.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, myError.Source); MessageBox.Show(errorMessage, "Error"); } }
public List <string> GetWorkSheet(string fileName, int worksheetNumber, string rangeStart, string rangeEnd) { // ->>>>> int userRow, int userCol // Reference to Excel Application. Excel.Application app = null; Excel.Workbooks books = null; Excel.Workbook book = null; Excel.Sheets sheets = null; Excel.Worksheet sheet = null; Excel.Range range = null; string startupPath = System.IO.Directory.GetCurrentDirectory(); try { app = new Excel.Application(); app.DisplayAlerts = false; books = app.Workbooks; //book = books.Open(Path.GetFullPath(Path.GetFullPath(@"TestData\" + fileName + ".xlsx"))); book = books.Open(fileName); sheets = book.Sheets; sheet = sheets.get_Item(1); range = sheet.get_Range(rangeStart, rangeEnd); //range.NumberFormat = "General"; //range.Value2 = data; object[,] cellValues = (object[, ])range.Value2; //List<double> lst = cellValues.Cast<object>().ToList().ConvertAll(x=> Convert.ToDouble(x)); List <string> lst = cellValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x)); book.Close(); app.Quit(); //Console.WriteLine(lst[0]); //Console.WriteLine(lst[1]); //Console.WriteLine(lst[2]); return(lst); } finally { if (range != null) { Marshal.ReleaseComObject(range); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } if (sheets != null) { Marshal.ReleaseComObject(sheets); } if (book != null) { Marshal.ReleaseComObject(book); } if (books != null) { Marshal.ReleaseComObject(books); } if (app != null) { Marshal.ReleaseComObject(app); } } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); //Excel.Application xlApp = new Excel.Application(); //Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(Path.GetFullPath(@"TestData\" + fileName + ".xlsx")); //Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(worksheetNumber); //Excel.Range xlRange = xlWorksheet.UsedRange; // //double[] valueArray = (double[])xlRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault); /* * * // Cleanup * xlWorkbook.Close(false); * xlApp.Quit(); * * // Manual disposal because of COM * while (Marshal.ReleaseComObject(xlApp) != 0) { } * while (Marshal.ReleaseComObject(xlWorkbook) != 0) { } * while (Marshal.ReleaseComObject(xlRange) != 0) { } * * xlApp = null; * xlWorkbook = null; * xlRange = null; * * GC.Collect(); * GC.WaitForPendingFinalizers(); * * */ }
private void createReportButton_Click(object sender, EventArgs e) { if (Directory.Exists(Settings.Default.FilesDir)) { createReportButton.Enabled = false; // Получаем список файлов в каталоге string[] filesArray = Directory.GetFiles(Settings.Default.FilesDir); foreach (string fileName in filesArray) { // Проверяем расширение файла FileInfo infoFile = new FileInfo(fileName); // Совпадает с датами формата: 1900-01-01 2007/08/13 1900.01.01 1900 01 01 1900-01.01 //string fileNamePattern = @"(19|20)\d\d([- /.])(0[1-9]|1[012])([- /.])(0[1-9]|[12][0-9]|3[01])"; string month; if (dateTimePicker1.Value.Month <= 9) { month = "0" + dateTimePicker1.Value.Month; } else { month = dateTimePicker1.Value.Month.ToString(); }; string fileNamePattern; if (checkBox1.Checked) { fileNamePattern = "(" + dateTimePicker1.Value.Year + @")([- /.])(" + month + @")([- /.])(0[1-9]|[12][0-9]|3[01])"; } else { fileNamePattern = "."; } // Обнуляем счётчик обработаных файлов filesCount = 0; if ((File.Exists(fileName)) && Regex.IsMatch(fileName, fileNamePattern) && (infoFile.Extension == ".dpm")) { filesCount++; // Создаём потоки чтения файлов. FileStream fileStream = new FileStream( fileName, FileMode.Open, FileAccess.Read ); StreamReader streamReader = new StreamReader( fileStream, Encoding.GetEncoding(1251) ); toolStripStatusLabel2.Text = "Обработка файла: " + infoFile.Name; // Считываем построчно файл до самого конца while (!streamReader.EndOfStream) { // Разделяем полученную строку // Структтура массива: // info[0] - знак выхождения или выхода трека (">" или "<") // info[1] - дата и время выхода пести // info[2] - идентификатор трека при составлении плейлиста // info[3] - псевдоним, который определяет положение треков на диске // info[4] - имя файла трека относительно псевдонима // info[5] - длительность трека string[] info = streamReader.ReadLine().Split('\t'); // Считаем только вхождение файла // Т.е. проверяем на символ ">" и проверяем только определённый псевдоним string[] aliases = Properties.Settings.Default.Aliases.Split(';'); foreach (string alias in aliases) { if ((info[0].Trim() == ">") && (info[3].Trim() == alias.Trim())) { if (!tableCount.Contains(info[4])) { // Если записи нет, создаём новую tableCount.Add(info[4], 1); tableDuration.Add(info[4], info[5]); } else { // Если запись есть, увеличиваем счётчик tableCount[info[4]] = (int)tableCount[info[4]] + 1; } } } } // Закрываем потоки чтения файлов. streamReader.Close(); fileStream.Close(); } // End If File Exist } // End Foreach FileNames // Меняем статус toolStripStatusLabel2.Text = "Обработано файлов: " + filesCount; // Создаём нумератор для управление Хэш массивом IDictionaryEnumerator tableCountEnum = tableCount.GetEnumerator(); toolStripStatusLabel2.Text = "Создание файла отчёта."; #region СОЗДАЁМ НОВЫЙ ДОКУМЕНТ EXCEL // Открываем приложение excelapp = new Excel.Application(); // Прячем окно программы excelapp.Visible = false; // Создаём документ с одной книгой excelapp.SheetsInNewWorkbook = 1; // Добавляем книгу excelapp.Workbooks.Add(Type.Missing); //Запрашивать сохранение //excelapp.DisplayAlerts = true; excelapp.DisplayAlerts = false; //Получаем набор ссылок на объекты Workbook (на созданные книги) excelappworkbooks = excelapp.Workbooks; //Получаем ссылку на книгу 1 - нумерация от 1 excelappworkbook = excelappworkbooks[1]; #endregion toolStripStatusLabel2.Text = "Генерация отчёта."; #region РАБОТА С ЯЧЕЙКАМИ excelsheets = excelappworkbook.Worksheets; // Получаем ссылку на лист 1 excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); #region Примеры: Выделение группы ячеек //excelcells = excelworksheet.get_Range("A1", "С10"); // Тоже //excelcells = excelworksheet.get_Range("A1", "С10").Cells; //excelcells = excelworksheet.get_Range("A1", "С10").Rows; //excelcells = excelworksheet.get_Range("A1", "С10").Cells; // Одну ячейку //excelcells = excelworksheet.get_Range("A1", "A1"); //excelcells = excelworksheet.get_Range("A1", Type.Missing); // Десять строк с 1 по 10ю //excelcells = (Excel.Range)excelworksheet.Rows["1:10", Type.Missing]; // Три столбца //excelcells = (Excel.Range)excelworksheet.Columns["A:C", Type.Missing]; // Одну строку //excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing]; // Один столбец //excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing]; #endregion // Выбираем первую ячейку excelcells = excelworksheet.get_Range("A1", Type.Missing).Cells; excelcells.Value2 = "Испольнитель"; excelcells = excelworksheet.get_Range("B1", Type.Missing).Cells; excelcells.Value2 = "Трек"; excelcells = excelworksheet.get_Range("C1", Type.Missing).Cells; excelcells.Value2 = "Длительность"; excelcells = excelworksheet.get_Range("D1", Type.Missing).Cells; excelcells.Value2 = "Количество"; excelcells = excelworksheet.get_Range("A1", "D1").Cells; excelcells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; #endregion #region ЗАПИСЫВАЕМ ДАННЫЕ В ФАЙЛ // Сбрасываем счётчик Хэша в начало tableCountEnum.Reset(); // В цикле перебираем все записи Хэша for (int i = 0; i < tableCount.Count; i++) { // Переход к следующей записи tableCountEnum.MoveNext(); // Выделяем имя файла из пути к файлу string trackName = Regex.Match(tableCountEnum.Key.ToString(), @"[^\\]*$").Value; // Отрезаем расширение файла trackName = trackName.Substring(0, trackName.Length - 4); // Заменяем тире с пробелами на знак % trackName = trackName.Replace("-", "%"); // Разделяем название группы и название трека string[] fullName = trackName.Split('%'); // Работаем с документом // Начинаем запись исполнителей со второй строки int m = i + 2; excelcells = (Excel.Range)excelworksheet.Cells[m, 1]; excelcells.Value2 = fullName[0].Trim(); excelcells = (Excel.Range)excelworksheet.Cells[m, 2]; if (fullName.Length > 1) {excelcells.Value2 = fullName[1].Trim(); } excelcells = (Excel.Range)excelworksheet.Cells[m, 3]; excelcells.Value2 = tableDuration[tableCountEnum.Key]; excelcells = (Excel.Range)excelworksheet.Cells[m, 4]; excelcells.Value2 = tableCountEnum.Value.ToString(); } #endregion toolStripStatusLabel2.Text = "Сохранение документа."; #region ЗАВЕРШАЕМ РАБОТУ С EXCEL //Ссылку можно получить и так, но тогда надо знать имена книг, //причем, после сохранения - знать расширение файла //excelappworkbook=excelappworkbooks["Книга 1"]; //Запроса на сохранение для книги не должно быть excelappworkbook.Saved = true; // ФОрмат сохранения документа excelapp.DefaultSaveFormat = Excel.XlFileFormat.xlWorkbookNormal; // Сохраняем книгу try { // Определяем имя путь сохранения файла // Если каталог указан, проверяем его корректность и сохраняем файл // Если каталог не указан или не существует, сохраняем отчёт в папке с программой string saveFileName; if (Directory.Exists(Settings.Default.ReportDir) && Settings.Default.ReportDir != "") { saveFileName = Settings.Default.ReportDir + "\\Report " + DateTime.Now.ToShortDateString() + ".xls"; } else { saveFileName = Application.StartupPath + "\\Report " + DateTime.Now.ToShortDateString() + ".xls"; } //excelappworkbook.Save(); excelappworkbook.SaveAs(saveFileName, Excel.XlFileFormat.xlWorkbookNormal); MessageBox.Show("Отчёт успешно сгенерирован и сохранён в файл: " + saveFileName, "Готово", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка сохранения файла отчёта.", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { // Закрываем приложение excelapp.Quit(); createReportButton.Enabled = true; } #endregion toolStripStatusLabel2.Text = ""; } // End if DirectoryExists else { // Выводим сообщение, если каталог отчётов не указан MessageBox.Show("Каталог с файлами отчётов не найден!", "Ошибка открытия каталога.", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
//1 - prvo učitamo stupce public void DobijStupce() { ExcelObj = new Excel.Application(); excelApp = new Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(dat, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Excel.Worksheet)sheets.get_Item(1); excelApp.Workbooks.Open(dat); for (int stupac = 1; stupac <= 25; stupac++) { var xlStupac = (worksheet.Cells[1, stupac] as Excel.Range).Value; string xlStupac_str = Convert.ToString(xlStupac); //za dobivanje stupca za kubike if (xlStupac_str == "Total Gross Volume") { stupacCBM = stupac; BrojStupca_CHR(stupacCBM); textBoxCBM.Text = stupac_chr.ToString(); } //else if (xlStupac_str.Contains("Volume")) //{ // stupacCBM = stupac; //} //za dobivanje stupca za ZIP else if (xlStupac_str == "Destination Postal Code") { stupacZIP = stupac; BrojStupca_CHR(stupacZIP); textBoxZIP.Text = stupac_chr.ToString(); } //else if (xlStupac_str.Contains("Postal")) // { // stupacZIP = stupac; // } else if (xlStupac_str == null) { stupacCijena = stupac; BrojStupca_CHR(stupacCijena); textBoxCijene.Text = stupac_chr.ToString(); break; } } }
static void Main(string[] args) { String[] Color = new String[57]; int file_counter = 0; Color[1] = "#000000"; Color[2] = "#FFFFFF"; Color[3] = "#FF0000"; Color[4] = "#00FF00"; Color[5] = "#0000FF"; Color[6] = "#FFFF00"; Color[7] = "#FF00FF"; Color[8] = "#00FFFF"; Color[9] = "#800000"; Color[10] = "#008000"; Color[11] = "#000080"; Color[12] = "#808000"; Color[13] = "#800080"; Color[14] = "#008080"; Color[15] = "#C0C0C0"; Color[16] = "#808080"; Color[17] = "#9999FF"; Color[18] = "#993366"; Color[19] = "#FFFFCC"; Color[20] = "#CCFFFF"; Color[21] = "#660066"; Color[22] = "#FF8080"; Color[23] = "#0066CC"; Color[24] = "#CCCCFF"; Color[25] = "#000080"; Color[26] = "#FF00FF"; Color[27] = "#FFFF00"; Color[28] = "#00FFFF"; Color[29] = "#800080"; Color[30] = "#800000"; Color[31] = "#008080"; Color[32] = "#0000FF"; Color[33] = "#00CCFF"; Color[34] = "#CCFFFF"; Color[35] = "#CCFFCC"; Color[36] = "#FFFF99"; Color[37] = "#99CCFF"; Color[38] = "#FF99CC"; Color[39] = "#CC99FF"; Color[40] = "#FFCC99"; Color[41] = "#3366FF"; Color[42] = "#33CCCC"; Color[43] = "#99CC00"; Color[44] = "#FFCC00"; Color[45] = "#FF9900"; Color[46] = "#FF6600"; Color[47] = "#666699"; Color[48] = "#969696"; Color[49] = "#003366"; Color[50] = "#339966"; Color[51] = "#003300"; Color[52] = "#333300"; Color[53] = "#993300"; Color[54] = "#993366"; Color[55] = "#333399"; Color[56] = "#333333"; char Context = ' '; char SubContext = ' '; string fileName = ""; string Path = ""; string Entity = ""; string Header = ""; string Footer = ""; StreamReader SR; string S; SR = File.OpenText("excel2xslt.config"); S = SR.ReadLine(); for (int i = 1; i <= 11; i++) { Console.WriteLine(S); S = SR.ReadLine(); if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 5) == "Files") { Path = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""); } if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 5) == "Excel") { fileName = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""); } if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Header") { Header = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""); } if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Footer") { Footer = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""); } if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 7) == "Context") { Context = Convert.ToChar(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"")); } if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 3) == "Sub") { SubContext = Convert.ToChar(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"")); } if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Entity") { Entity = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""); } } SR.Close(); if (Context == ' ') { Console.WriteLine("Context not defined. Please update the value in excel2xslt.config"); } ; if (SubContext == ' ') { Console.WriteLine("SubContext not defined. Please update the value in excel2xslt.config"); } ; if (fileName == "") { Console.WriteLine("FileName not defined. Please update the value in excel2xslt.config"); } ; if (Path == "") { Console.WriteLine("Path not defined. Please update the value in excel2xslt.config"); } ; if (Entity == "") { Console.WriteLine("Entity not defined. Please update the value in excel2xslt.config"); } ; if (Header == "") { Console.WriteLine("Header not defined. Please update the value in excel2xslt.config"); } ; if (Footer == "") { Console.WriteLine("Footer not defined. Please update the value in excel2xslt.config"); } ; Excel.Application excelApp = new Excel.ApplicationClass(); // Creates a new Excel Application excelApp.Visible = false; // visible string workbookPath = Path + fileName; Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Console.WriteLine(); Console.WriteLine("Output files written to: " + Path); StreamWriter swXMLCustomCfg = new StreamWriter(Path + "MailCustomCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1")); StreamWriter swXMLHeaderCfg = new StreamWriter(Path + "MailHeaderCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1")); StreamWriter swXMLConstants = new StreamWriter(Path + "Constants_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1")); swXMLCustomCfg.WriteLine("<?xml version='1.0' encoding='ISO-8859-1' standalone='yes'?>"); swXMLCustomCfg.WriteLine("<ResultSet xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>"); swXMLHeaderCfg.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>"); swXMLHeaderCfg.WriteLine("<ResultSet>"); swXMLConstants.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>"); swXMLConstants.WriteLine("<root>"); // The following gets the Worksheets collection Excel.Sheets excelSheets = excelWorkbook.Worksheets; //Console.WriteLine("No of sheets"+excelSheets.Count); for (int s = 1; s <= excelSheets.Count; s++) { Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(s); StreamWriter sw = new StreamWriter(Path + excelWorksheet.Name + ".xsl", false, System.Text.Encoding.GetEncoding("ISO-8859-1")); swXMLConstants.WriteLine(" <Constant>"); swXMLConstants.WriteLine(" <DictDetail_Name Type='String'>" + excelWorksheet.Name + "</DictDetail_Name>"); swXMLConstants.WriteLine(" <DictDetail_ShortName Type='String'>" + excelWorksheet.Name + "</DictDetail_ShortName>"); swXMLConstants.WriteLine(" <ElementOwner Type='Character'>C</ElementOwner>"); swXMLConstants.WriteLine(" <DictCoherency_Name Type='String'>XSLTFileName</DictCoherency_Name>"); swXMLConstants.WriteLine(" <ReturnType Type='Character'>S</ReturnType>"); swXMLConstants.WriteLine(" <Description Type='String'/>"); swXMLConstants.WriteLine(" <Sorter Type='String'>XSLT</Sorter>"); swXMLConstants.WriteLine(" <StringValue Type='EmptyString'>" + excelWorksheet.Name + "</StringValue>"); swXMLConstants.WriteLine(" <Context Type='Character'>" + Context + "</Context>"); swXMLConstants.WriteLine(" </Constant>"); swXMLHeaderCfg.WriteLine(" <MailHeaderCfg>"); swXMLHeaderCfg.WriteLine(" <MailHeaderCfg_Name type='KDB_FIELD_NAME_0032'>" + excelWorksheet.Name + "</MailHeaderCfg_Name>"); swXMLHeaderCfg.WriteLine(" <Description type='string'/>"); swXMLHeaderCfg.WriteLine(" <CFG_Receiver type='string'>EDD_ThirdParty_Id_Cpty</CFG_Receiver>"); swXMLHeaderCfg.WriteLine(" <MailType_Name type='string'>Pdf Confirmation</MailType_Name>"); swXMLHeaderCfg.WriteLine(" <CFG_Master type='string'>EmptyEvent</CFG_Master>"); swXMLHeaderCfg.WriteLine(" <CFG_FaxAddress type='string'>DummyElement</CFG_FaxAddress>"); swXMLHeaderCfg.WriteLine(" <CFG_EmailFrom type='string'>DummyElement</CFG_EmailFrom>"); swXMLHeaderCfg.WriteLine(" <CFG_EmailTo type='string'>DummyElement</CFG_EmailTo>"); swXMLHeaderCfg.WriteLine(" <CFG_EmailCC type='string'>DummyElement</CFG_EmailCC>"); swXMLHeaderCfg.WriteLine(" <CFG_Bic type='string'>EVB_Bic_Id</CFG_Bic>"); swXMLHeaderCfg.WriteLine(" <ToBePrinted type='YesNo_t'>Y</ToBePrinted>"); swXMLHeaderCfg.WriteLine(" <NumberOfCopies type='KDB_FIELD_INTEGER'>1</NumberOfCopies>"); swXMLHeaderCfg.WriteLine(" <CFG_ExternalRef type='string'>NONE</CFG_ExternalRef>"); swXMLHeaderCfg.WriteLine(" <CFG_AddAddress type='string'>DummyElement</CFG_AddAddress>"); swXMLHeaderCfg.WriteLine(" <CFG_TypeOfOper type='string'>TAG22A</CFG_TypeOfOper>"); swXMLHeaderCfg.WriteLine(" <CFG_ReleaseDate type='string'>DummyElement</CFG_ReleaseDate>"); swXMLHeaderCfg.WriteLine(" <CFG_DeadLine type='string'>DummyElement</CFG_DeadLine>"); swXMLHeaderCfg.WriteLine(" <ValidityDateBegin type='KDB_FIELD_DATE'>2007/03/09</ValidityDateBegin>"); swXMLHeaderCfg.WriteLine(" <ValidityDateEnd type='KDB_FIELD_DATE'>2100/03/09</ValidityDateEnd>"); swXMLHeaderCfg.WriteLine(" <Priority type='BOPriority_t'>N</Priority>"); swXMLHeaderCfg.WriteLine(" <CFG_Entity type='string'>EBO_Entity_Id</CFG_Entity>"); swXMLHeaderCfg.WriteLine(" <AutoSending type='YesNo_t'>N</AutoSending>"); swXMLHeaderCfg.WriteLine(" <CFG_XSLT type='string'>" + excelWorksheet.Name + "</CFG_XSLT>"); swXMLHeaderCfg.WriteLine(" <Context type='BOContext_t'>" + Context + "</Context>"); swXMLHeaderCfg.WriteLine(" <SubContext type='BOContext_t'>" + SubContext + "</SubContext>"); swXMLHeaderCfg.WriteLine(" <CFG_Description type='string'>ModelDescripMails</CFG_Description>"); swXMLHeaderCfg.WriteLine(" <Entity_Name type='string'>" + Entity + "</Entity_Name>"); swXMLHeaderCfg.WriteLine(" <Validity type='BOValidity_t'>A</Validity>"); swXMLHeaderCfg.WriteLine(" </MailHeaderCfg>"); Excel.Range cells = (Excel.Range)excelWorksheet.get_Range("A1", excelWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing)); int[] actual_columns = new int[cells.Rows.Count + 1]; for (int i = 1; i <= cells.Rows.Count; i++) { actual_columns[i] = 0; for (int j = 1; j <= cells.Columns.Count; j++) { Excel.Range current = (Excel.Range)cells[i, j]; if (current.Value2 != null) { actual_columns[i] = j; } ; } ; //Console.WriteLine(actual_columns[i]); } ; sw.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>"); sw.WriteLine("<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform' xmlns:fo='http://www.w3.org/1999/XSL/Format' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:java='http://xml.apache.org/xslt/java' exclude-result-prefixes='java' xmlns:date='http://exslt.org/dates-and-times'"); sw.WriteLine(">"); sw.WriteLine(" <xsl:include href='" + Header + "'/>"); sw.WriteLine(" <xsl:include href='" + Footer + "'/> "); sw.WriteLine(""); sw.WriteLine(" <xsl:template match='/'>"); sw.WriteLine("<fo:root xmlns:fo='http://www.w3.org/1999/XSL/Format'>"); sw.WriteLine(" <fo:layout-master-set>"); sw.WriteLine(" <fo:simple-page-master master-name='single_page' page-height='297mm' page-width='210mm' >"); sw.WriteLine(" <fo:region-body region-name='xsl-region-body' "); sw.WriteLine(" margin='20mm' />"); sw.WriteLine(" </fo:simple-page-master>"); sw.WriteLine(" <fo:page-sequence-master master-name='repeatable_master'>"); sw.WriteLine(" <fo:repeatable-page-master-reference master-reference='single_page' maximum-repeats='10'/>"); sw.WriteLine(" </fo:page-sequence-master>"); sw.WriteLine(" </fo:layout-master-set>"); sw.WriteLine(""); sw.WriteLine(" <fo:page-sequence master-reference='repeatable_master'>"); sw.WriteLine(" <fo:flow flow-name='xsl-region-body'>"); sw.WriteLine(" "); sw.WriteLine(" <fo:table font-family='Times New Roman' font-size='10.00pt' width='180.00mm'>"); sw.WriteLine(" <fo:table-column column-width='180mm'/>"); sw.WriteLine(" "); sw.WriteLine(" <fo:table-body>"); sw.WriteLine(" <fo:table-row height='55mm'>"); sw.WriteLine(" <fo:table-cell padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>"); sw.WriteLine(" <xsl:call-template name='HEADER'/> "); sw.WriteLine(" <fo:block height='20mm' text-align='left'></fo:block>"); sw.WriteLine(" </fo:table-cell>"); sw.WriteLine(" </fo:table-row>"); sw.WriteLine(" </fo:table-body>"); sw.WriteLine(" </fo:table>"); /* second table - body */ sw.WriteLine(" <fo:table font-family='Times New Roman' font-size='10.00pt' width='180.00mm'>"); for (int i = 1; i <= cells.Columns.Count; i++) { sw.WriteLine("<fo:table-column column-width='" + 180 / cells.Columns.Count + "mm'/>"); } sw.WriteLine(" <fo:table-body>"); int[] total_columns = new int[cells.Rows.Count + 1]; string[] choose = new string[cells.Rows.Count + 1]; for (int i = 1; i <= cells.Rows.Count; i++) { total_columns[i] = 0; Excel.Range current = (Excel.Range)cells[i, 1]; if (current.Value2 != null) { if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 6) == "choose") { choose[i] = "<xsl:choose>"; } else { if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 4) == "when") { choose[i] = "<xsl:when test=\"" + (string)XsltFunctions.SubstringAfter(current.Value2.ToString(), "when ") + "\">"; /* if (XsltFunctions.Substring((string)XsltFunctions.SubstringAfter(current.Value2.ToString(),"when "),1,8) == "MailBody") * { * swXMLCustomCfg.WriteLine(" <MailCustomCfg>"); * swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>"+excelWorksheet.Name+"</MailHeaderCfg_Name>"); * swXMLCustomCfg.WriteLine(" <MailCustomTag type='string'>"+XsltFunctions.SubstringAfter(XsltFunctions.SubstringBefore((string)XsltFunctions.SubstringAfter(current.Value2.ToString(),"when ")," and "),"MailBody/")+"</MailCustomTag>"); * swXMLCustomCfg.WriteLine(" <Comment type='string'>/</Comment>"); * swXMLCustomCfg.WriteLine(" <CFG_Element type='string'>Empty</CFG_Element>"); * swXMLCustomCfg.WriteLine(" <SortingNum type='KDB_FIELD_INTEGER'></SortingNum>"); * swXMLCustomCfg.WriteLine(" </MailCustomCfg>"); * }; * if (XsltFunctions.Substring((string)XsltFunctions.SubstringAfter(current.Value2.ToString()," and "),1,8) == "MailBody") * { * swXMLCustomCfg.WriteLine(" <MailCustomCfg>"); * swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>"+excelWorksheet.Name+"</MailHeaderCfg_Name>"); * swXMLCustomCfg.WriteLine(" <MailCustomTag type='string'>"+XsltFunctions.SubstringAfter((string)XsltFunctions.SubstringAfter(current.Value2.ToString()," and "),"MailBody/")+"</MailCustomTag>"); * swXMLCustomCfg.WriteLine(" <Comment type='string'>/</Comment>"); * swXMLCustomCfg.WriteLine(" <CFG_Element type='string'>Empty</CFG_Element>"); * swXMLCustomCfg.WriteLine(" <SortingNum type='KDB_FIELD_INTEGER'></SortingNum>"); * swXMLCustomCfg.WriteLine(" </MailCustomCfg>"); * } */ } else { if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 7) == "endwhen") { choose[i] = "</xsl:when>"; } else { if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 9) == "endchoose") { choose[i] = "</xsl:choose>"; } else { if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 9) == "otherwise") { choose[i] = "<xsl:otherwise>"; } else { if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 12) == "endotherwise") { choose[i] = "</xsl:otherwise>"; } else { choose[i] = ""; }; }; }; } }; }; } ; } ; for (int i = 1; i <= cells.Rows.Count; i++) { int span = 1; int mergedcellno = 0; int t = 0; if (choose[i] != "") { sw.WriteLine(choose[i]); } else { sw.WriteLine("<fo:table-row height='5mm'>"); for (int j = 1; j <= cells.Columns.Count && total_columns[i] < cells.Columns.Count; j++) { if (mergedcellno != 0 && t < mergedcellno && t != 0) { t = t + 1; } else { if (t >= mergedcellno) { t = 0; mergedcellno = 0; } ; }; if (t == 0) { String color; Excel.Range current = (Excel.Range)cells[i, j]; //Console.WriteLine("Color "+current.Interior.ColorIndex); if ((int)current.Interior.ColorIndex >= 1 && (int)current.Interior.ColorIndex <= 56) { color = Color[(int)current.Interior.ColorIndex]; } else { color = "white"; }; if (current.Value2 != null) { if ((bool)current.MergeCells) { //Console.WriteLine(current.get_Address("", "", Excel.XlReferenceStyle.xlR1C1,"", "")+"Row "+i+" Column "+j+" merged"+":"+current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1,"", "")); span = Convert.ToInt32(XsltFunctions.SubstringAfter(XsltFunctions.SubstringAfter(current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1, "", ""), ":"), "C")) - Convert.ToInt32(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1, "", ""), "C"), ":")) + 1; mergedcellno = span; } else { span = 1; }; sw.WriteLine(" <fo:table-cell number-columns-spanned='"+ span + "' background-color='" + color + "' border-color='black' border-width='0.2pt' padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>"); sw.WriteLine(" <fo:block height='5mm' text-align='left'>"); if ((bool)XsltFunctions.Contains(current.Value2.ToString(), "MailBody") == true) { if ((bool)(XsltFunctions.Contains(current.Value2.ToString(), "Amount")) == true || (bool)(XsltFunctions.Contains(current.Value2.ToString(), "Rate")) == true) { sw.WriteLine(" <xsl:call-template name='Amount'>"); sw.WriteLine(" <xsl:with-param name='Amount' select='"+ current.Value2.ToString() + "'/>"); sw.WriteLine(" </xsl:call-template>"); } else { if ((bool)(XsltFunctions.Contains(current.Value2.ToString(), "Date")) == true) { sw.WriteLine(" <xsl:call-template name='Date'>"); sw.WriteLine(" <xsl:with-param name='Date' select='"+ current.Value2.ToString() + "'/>"); sw.WriteLine(" </xsl:call-template>"); } else { sw.WriteLine(" "+ "<xsl:value-of select = '" + current.Value2.ToString() + "'/>"); } }; swXMLCustomCfg.WriteLine(" <MailCustomCfg>"); swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>" + excelWorksheet.Name + "</MailHeaderCfg_Name>"); swXMLCustomCfg.WriteLine(" <MailCustomTag type='string'>"+ XsltFunctions.SubstringAfter(current.Value2.ToString(), "MailBody/") + "</MailCustomTag>"); swXMLCustomCfg.WriteLine(" <Comment type='string'>/</Comment>"); swXMLCustomCfg.WriteLine(" <CFG_Element type='string'>Empty</CFG_Element>"); swXMLCustomCfg.WriteLine(" <SortingNum type='KDB_FIELD_INTEGER'></SortingNum>"); swXMLCustomCfg.WriteLine(" </MailCustomCfg>"); } else { sw.WriteLine(" "+ current.Value2.ToString()); }; //Console.WriteLine(current.Value2); sw.WriteLine(" </fo:block>"); sw.WriteLine(" </fo:table-cell>"); } else { sw.WriteLine(" <fo:table-cell number-columns-spanned='"+ span + "' background-color='" + color + "' border-color='black' border-width='0.2pt' padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>"); sw.WriteLine(" <fo:block height='5mm' text-align='left'>"); sw.WriteLine(" </fo:block>"); sw.WriteLine(" </fo:table-cell>"); }; total_columns[i] = total_columns[i] + span; span = 1; } if (mergedcellno != 0 && t < mergedcellno) { t = t + 1; } ; } sw.WriteLine(" </fo:table-row>"); sw.WriteLine(); }; } sw.WriteLine(" </fo:table-body>"); sw.WriteLine(" </fo:table>"); sw.WriteLine(" /* Placeholder for footer */"); sw.WriteLine(" "); sw.WriteLine(" <fo:table font-family='Times New Roman' font-size='10.00pt' width='120.00mm'>"); sw.WriteLine(" <fo:table-column column-width='120mm'/>"); sw.WriteLine(" "); sw.WriteLine(" <fo:table-body start-indent='0pt'>"); sw.WriteLine(" "); sw.WriteLine(" <fo:table-row height='100mm'>"); sw.WriteLine(" <fo:table-cell padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>"); sw.WriteLine(" <xsl:call-template name='FOOTER'/>"); sw.WriteLine(" <fo:block height='20mm' text-align='left'> </fo:block>"); sw.WriteLine(" </fo:table-cell>"); sw.WriteLine(" </fo:table-row>"); sw.WriteLine(" </fo:table-body>"); sw.WriteLine(" </fo:table> "); sw.WriteLine(" </fo:flow>"); sw.WriteLine(" </fo:page-sequence>"); sw.WriteLine("</fo:root>"); sw.WriteLine(""); sw.WriteLine("</xsl:template>"); sw.WriteLine("</xsl:stylesheet>"); sw.Close(); file_counter++; Console.WriteLine(file_counter + ". XSL file successfully written to: " + excelWorksheet.Name + ".xsl"); } swXMLCustomCfg.WriteLine("</ResultSet>"); swXMLCustomCfg.Close(); file_counter++; Console.WriteLine(file_counter + ". MailCustomCfg file successfully written to: " + "MailCustomCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml"); swXMLHeaderCfg.WriteLine("</ResultSet>"); swXMLHeaderCfg.Close(); file_counter++; Console.WriteLine(file_counter + ". MailHeaderCfg file successfully written to: " + "MailHeaderCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml"); swXMLConstants.WriteLine("</root>"); swXMLConstants.Close(); file_counter++; Console.WriteLine(file_counter + ". Constants file successfully written to: " + "Constants_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml"); excelWorkbook.Close(false, 0, 0); }
private static void openExcel(String input, String output) { excelapp1 = new Microsoft.Office.Interop.Excel.Application(); myworkbook1 = excelapp1.Workbooks.Open(input, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); myworksheets1 = myworkbook1.Worksheets; myworksheet1 = myworksheets1.get_Item(1); range1 = myworksheet1.UsedRange; excelapp2 = new Microsoft.Office.Interop.Excel.Application(); myworkbook2 = excelapp2.Workbooks.Open(output, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); myworksheets2 = myworkbook2.Worksheets; myworksheet2 = myworksheets2.get_Item(1); range2 = myworksheet2.UsedRange; }
/// <summary> /// 英文版 /// </summary> /// <param name="AppDT"></param> /// <param name="DetailDT"></param> /// <returns></returns> private bool CreateEnXLS(DataTable AppDT, DataTable DetailDT) { string XLSName; decimal MoneyDiscont = 1; decimal MaxMoney = 0; string strMoneyUnit = ""; double douTotalPrice = 0.00; switch (AppDT.Rows[0]["MoneyUnit"].ToString()) { case "1": default: MoneyDiscont = 1; break; case "2": MoneyDiscont = EmailControl.config.USrate; MaxMoney = EmailControl.config.USD; strMoneyUnit = "US"; break; case "3": MoneyDiscont = EmailControl.config.HKrate; MaxMoney = EmailControl.config.HKD; strMoneyUnit = "HKD"; break; case "4": MoneyDiscont = EmailControl.config.MOPrate; MaxMoney = EmailControl.config.MOP; strMoneyUnit = "MOP"; break; case "5": MoneyDiscont = EmailControl.config.SGDrate; MaxMoney = EmailControl.config.SGD; strMoneyUnit = "SGD"; break; case "6": MoneyDiscont = EmailControl.config.MYRrate; MaxMoney = EmailControl.config.MYR; strMoneyUnit = "MYR"; break; case "7": MoneyDiscont = EmailControl.config.GBPrate; MaxMoney = EmailControl.config.GBP; strMoneyUnit = "GBP"; break; case "8": MoneyDiscont = EmailControl.config.EURrate; MaxMoney = EmailControl.config.EUR; strMoneyUnit = "EUR"; break; case "9": MoneyDiscont = EmailControl.config.JPYrate; MaxMoney = EmailControl.config.JPY; strMoneyUnit = "JPY"; break; case "10": MoneyDiscont = EmailControl.config.TWDrate; MaxMoney = EmailControl.config.TWD; strMoneyUnit = "TWD"; break; } string strSalesDate; if (AppDT.Rows[0]["ApplicantsDate"].ToString() != null && AppDT.Rows[0]["ApplicantsDate"].ToString() != "") { strSalesDate = DateTime.Parse(AppDT.Rows[0]["SalesDate"].ToString()).ToShortDateString(); } else { strSalesDate = DateTime.Now.ToShortDateString(); } XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\2015 Staff Purchase Form-in HK -templet .xls"; Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbooks wbks = app.Workbooks; Excel._Workbook _wbk = wbks.Add(XLSName); Excel.Sheets shs = _wbk.Sheets; Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1); //写入 _wsh.Cells[4, 3] = AppDT.Rows[0]["ApplicantsName"].ToString(); _wsh.Cells[4, 11] = AppDT.Rows[0]["ApplicantsNo"].ToString(); _wsh.Cells[5, 3] = AppDT.Rows[0]["Location"].ToString(); _wsh.Cells[5, 11] = AppDT.Rows[0]["PurchaseLocation"].ToString(); _wsh.Cells[22, 2] = "For gift purchases of items with unit RSP exceeding " + strMoneyUnit + MaxMoney + ", please complete the following :"; _wsh.Cells[22, 11] = " Total " + strMoneyUnit + ":"; //_wsh.Cells[22, 12] = AppDT.Rows[0]["TotalPrice"].ToString(); _wsh.Cells[24, 12] = AppDT.Rows[0]["TransNo"].ToString(); //_wsh.Cells[23, 12] = strSalesDate; _wsh.Cells[31, 5] = AppDT.Rows[0]["ApplicantsName"].ToString(); if (AppDT.Rows[0]["ApplicantsDate"].ToString() != null && AppDT.Rows[0]["ApplicantsDate"].ToString() != "" && AppDT.Rows[0]["ApplicantsName"].ToString() != "") { _wsh.Cells[31, 7] = DateTime.Parse(AppDT.Rows[0]["ApplicantsDate"].ToString()).ToShortDateString(); } _wsh.Cells[32, 5] = AppDT.Rows[0]["ApprovalName2"].ToString(); if (AppDT.Rows[0]["ApprovalDate2"].ToString() != null && AppDT.Rows[0]["ApprovalDate2"].ToString() != "" && AppDT.Rows[0]["ApprovalName2"].ToString() != "") { _wsh.Cells[32, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate2"].ToString()).ToShortDateString(); } _wsh.Cells[33, 5] = AppDT.Rows[0]["ApprovalName"].ToString(); if (AppDT.Rows[0]["ApprovalDate"].ToString() != null && AppDT.Rows[0]["ApprovalDate"].ToString() != "" && AppDT.Rows[0]["ApprovalName"].ToString() != "") { _wsh.Cells[33, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate"].ToString()).ToShortDateString(); } _wsh.Cells[34, 5] = AppDT.Rows[0]["ApprovalName3"].ToString(); if (AppDT.Rows[0]["ApprovalDate3"].ToString() != null && AppDT.Rows[0]["ApprovalDate3"].ToString() != "" && AppDT.Rows[0]["ApprovalName3"].ToString() != "") { _wsh.Cells[34, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate3"].ToString()).ToShortDateString(); } _wsh.Cells[35, 5] = AppDT.Rows[0]["StaffName"].ToString(); _wsh.Cells[36, 2] = "Approved by HK Finance Manager:\r\n (If unit RSP exceeding " + strMoneyUnit + MaxMoney + ")"; if (AppDT.Rows[0]["StaffName"].ToString() != "") { _wsh.Cells[35, 7] = strSalesDate; _wsh.Cells[23, 12] = strSalesDate; } else { _wsh.Cells[35, 7] = ""; _wsh.Cells[23, 12] = ""; } int j = 0; int i = 0; foreach (DataRow dr in DetailDT.Rows) { if (i < 6) { _wsh.Cells[8 + 2 * i, 2] = dr["CodeID"].ToString(); _wsh.Cells[8 + 2 * i, 4] = dr["ItemID"].ToString(); _wsh.Cells[8 + 2 * i, 5] = dr["Detail"].ToString(); _wsh.Cells[8 + 2 * i, 7] = dr["Count"].ToString(); _wsh.Cells[8 + 2 * i, 8] = dr["Price"].ToString(); if (dr["SelforGift"].ToString() == "2") { _wsh.Cells[8 + 2 * i, 9] = "Gift"; } else { _wsh.Cells[8 + 2 * i, 9] = "Self"; } _wsh.Cells[8 + 2 * i, 10] = dr["ApprovalCount"].ToString(); _wsh.Cells[8 + 2 * i, 11] = dr["ApprovalDiscount"].ToString(); _wsh.Cells[8 + 2 * i, 12] = dr["FinalPrice"].ToString(); douTotalPrice = douTotalPrice + Convert.ToDouble(dr["FinalPrice"].ToString()); i++; } if (j < 3) { if (dr["SelforGift"].ToString() == "2") { _wsh.Cells[24 + j, 2] = dr["CodeID"].ToString(); _wsh.Cells[24 + j, 3] = dr["Recipient"].ToString(); _wsh.Cells[24 + j, 5] = dr["Relationship"].ToString(); _wsh.Cells[24 + j, 6] = dr["Reason"].ToString(); j++; } } } _wsh.Cells[22, 12] = douTotalPrice; //保存 string filePath = System.IO.Directory.GetCurrentDirectory() + @"\tempPDF\tempExcel.xls"; app.AlertBeforeOverwriting = false; _wbk.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //退出和释放 _wbk.Close(null, null, null); wbks.Close(); app.Quit(); //释放掉多余的excel进程 System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; return(true); }
//---> 'workforceSelect_Click' /* Controls the open file dialog for selecting a workforce Excel file. Will perform error checking * after file is selected to ensure the workforce file is formatted appropriately. Once successfully * loaded, error checking on the record information (gender, A/J utilization, and EEO) and checking * on total sheet hours versus total weekly reported hours will be performed. If any error checks * fail, information about the failure will be displayed on the message panel. Also, any errors * encounterd must be fixed by the user. */ private void workforceSelect_Click(object sender, EventArgs e) { //clear previous program state this.clearWindow(); OpenFileDialog workforceDialog = new OpenFileDialog(); //OpenFileDialog properties workforceDialog.InitialDirectory = "c:\\"; workforceDialog.Filter = "Excel Files (*.xls; *.xlsx)|*.xls; *.xlsx"; workforceDialog.FilterIndex = 2; workforceDialog.RestoreDirectory = true; //sentinel statement //will also perform error checking on file properties if (workforceDialog.ShowDialog() == DialogResult.OK) { this.msgOut.Text = "W O R K F O R C E F I L E\n"; //show file name in mainWindow this.workforceName.Text = Path.GetFileName(workforceDialog.FileName); workforceFile = new Excel.Application(); //check if file is already open try { workforceBook = workforceFile.Workbooks.OpenXML(workforceDialog.FileName, Type.Missing, Type.Missing); } catch (Exception error_1) { this.msgOut.Text += "File Is Already Open!\nError:\n" + error_1 + "\n\n"; this.killWorkforce(); return; } this.msgOut.Text += "\"Open File\" Check Passed!\n"; //display check passed message workforceSheets = workforceBook.Worksheets; //assign once checked for errors //check for proper tab formatting try { workforceSheets.get_Item(DateTime.Now.Year.ToString()); } catch (Exception error_2) { this.msgOut.Text += "Improper Tab Name Formatting!\nError:\n" + error_2 + "\n\n"; this.killWorkforce(); return; } this.msgOut.Text += "\"Tab Name\" Check Passed!\n"; //display check passed message //verify gender, eeo, and a/j status in workforce file //will also calculate the number of records in the workforce file if (!this.verifyWorksheet(workforceSheets.get_Item(DateTime.Now.Year.ToString()))) { MessageBox.Show("Fix errors on workforce file " + Path.GetFileName(workforceDialog.FileName) + " before continuing", "Record Errors: " + Path.GetFileNameWithoutExtension(workforceDialog.FileName), MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); this.killWorkforce(); } else { this.msgOut.Text += "No Errors Found!\n\n"; this.programLocation.Text = "Ready for Master Records File"; } return; } }
public void WriteToExcel(DataTable thisTable, string FileName, string sheetName) { string strFilePath = FileName; string XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\报告模板.xls"; Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbooks wbks = app.Workbooks; Excel._Workbook _wbk = wbks.Add(XLSName); Excel.Sheets shs = _wbk.Sheets; Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1); try { int sheetRowsCount = _wsh.UsedRange.Rows.Count; int count = thisTable.Columns.Count; //设置列名 //foreach (DataColumn myNewColumn in thisTable.Columns) //{ // _wsh.Cells[0, count] = myNewColumn.ColumnName; // count = count + 1; //}er for (int i = 0; i < count; i++) { _wsh.Cells[1, i + 1] = thisTable.Columns[i].ColumnName; } //加入內容 for (int i = 1; i <= thisTable.Rows.Count; i++) { for (int j = 1; j <= thisTable.Columns.Count; j++) { _wsh.Cells[i + sheetRowsCount, j] = thisTable.Rows[i - 1][j - 1]; } } _wsh.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; _wsh.Cells.Columns.AutoFit(); _wsh.Cells.Rows.AutoFit(); //若為EXCEL2000, 將最後一個參數拿掉即可 _wbk.SaveAs(strFilePath, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null); } catch (Exception ex) { } finally { //關閉文件 _wbk.Close(false, Type.Missing, Type.Missing); app.Workbooks.Close(); app.Quit(); //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wsh); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); _wsh = null; _wbk = null; app = null; } }
private void diagram_Click(object sender, EventArgs e) { KURS.allDataSet kursds = new KURS.allDataSet(); KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter(); zta.Fill(kursds.Zakaz); // object misValue = System.Reflection.Missing.Value; // Excel.Application excelapp = new Excel.Application(); excelapp.Visible = true; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(misValue); Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelappworkbooks[1]; Excel.Sheets excelsheets = excelappworkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); //excelworksheet.Activate(); // var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p; int count = 0; excelapp.Cells[1][1] = "date"; excelapp.Cells[2][1] = "summ"; foreach (var p in c) { count++; } // int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0; while (i < count) { foreach (var p in c) { a[i] = p.Data.Day; if (i == 0) { sum += p.Summ; } if (i > 0) { if (a[i] == a[i - 1]) { sum += p.Summ; } else { b[i - 1] = sum; sum = p.Summ; i++; } } else { i++; } } b[i - 1] = sum; count = i; i = 0; break; } Dictionary <int, double> dic = new Dictionary <int, double>(); while (i < count) { excelapp.Cells[1][i + 2] = a[i]; excelapp.Cells[2][i + 2] = b[i]; dic.Add(a[i], b[i]); i++; } // string str = (count + 1).ToString(); // //Excel.Window excelWindow = null; Excel.Range excelcells = null; //Определяем диаграммы как объекты Excel.ChartObjects Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing); //Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300); Excel.Chart chartPage = chartsobjrct.Chart; excelcells = excelworksheet.get_Range("B1", "B" + str); chartPage.SetSourceData(excelcells, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.HasTitle = true; axis.AxisTitle.Text = "Data"; // Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.XValues = a; // excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); excelappworkbook.Close(true, misValue, misValue); excelapp.Quit(); this.chart1.Titles.Add("Отчет за месяц"); this.chart1.Series[0].Points.Clear(); Series ser = this.chart1.Series[0]; ser.LegendText = "Summ"; foreach (KeyValuePair <int, double> pair in dic) { ser.Points.AddXY(pair.Key, pair.Value); } // }
private bool CreateXLS(DataTable AppDT, DataTable DetailDT, string title) { double douTotalPrice = 0.00; string XLSName; XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\2016国内员购申请表-模板.xls"; Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbooks wbks = app.Workbooks; Excel._Workbook _wbk = wbks.Add(XLSName); Excel.Sheets shs = _wbk.Sheets; Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1); //写入 _wsh.Cells[2, 2] = title; _wsh.Cells[4, 3] = AppDT.Rows[0]["ApplicantsName"].ToString(); _wsh.Cells[4, 10] = AppDT.Rows[0]["ApplicantsNo"].ToString(); _wsh.Cells[5, 3] = AppDT.Rows[0]["Location"].ToString(); _wsh.Cells[5, 10] = AppDT.Rows[0]["PurchaseLocation"].ToString(); _wsh.Cells[22, 11] = AppDT.Rows[0]["TotalPrice"].ToString(); _wsh.Cells[24, 11] = AppDT.Rows[0]["TransNo"].ToString(); _wsh.Cells[23, 11] = DateTime.Now.ToShortDateString(); _wsh.Cells[31, 4] = AppDT.Rows[0]["ApplicantsName"].ToString(); _wsh.Cells[31, 6] = DateTime.Parse(AppDT.Rows[0]["ApplicantsDate"].ToString()).ToShortDateString(); _wsh.Cells[32, 4] = AppDT.Rows[0]["ApprovalName2"].ToString(); _wsh.Cells[32, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate2"].ToString()).ToShortDateString(); _wsh.Cells[33, 4] = AppDT.Rows[0]["ApprovalName"].ToString(); _wsh.Cells[33, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate"].ToString()).ToShortDateString(); _wsh.Cells[34, 4] = AppDT.Rows[0]["ApprovalName3"].ToString(); if (AppDT.Rows[0]["ApprovalName3"].ToString() != "") { _wsh.Cells[34, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate3"].ToString()).ToShortDateString(); } else { _wsh.Cells[34, 6] = ""; } int j = 0; int i = 0; foreach (DataRow dr in DetailDT.Rows) { if (i < 6) { _wsh.Cells[9 + 2 * i, 2] = dr["CodeID"].ToString(); _wsh.Cells[9 + 2 * i, 3] = dr["ItemID"].ToString(); _wsh.Cells[9 + 2 * i, 4] = dr["Detail"].ToString(); _wsh.Cells[9 + 2 * i, 6] = dr["Count"].ToString(); _wsh.Cells[9 + 2 * i, 7] = dr["Price"].ToString(); if (dr["SelforGift"].ToString() == "2") { _wsh.Cells[9 + 2 * i, 8] = "送礼"; } else { _wsh.Cells[9 + 2 * i, 8] = "自用"; } _wsh.Cells[9 + 2 * i, 9] = dr["ApprovalCount"].ToString(); _wsh.Cells[9 + 2 * i, 10] = dr["ApprovalDiscount"].ToString(); _wsh.Cells[9 + 2 * i, 11] = dr["FinalPrice"].ToString(); i++; douTotalPrice += double.Parse(dr["FinalPrice"].ToString()); } if (j < 3) { if (dr["SelforGift"].ToString() == "2") { _wsh.Cells[24 + j, 2] = dr["CodeID"].ToString(); _wsh.Cells[24 + j, 3] = dr["Recipient"].ToString(); _wsh.Cells[24 + j, 4] = dr["Relationship"].ToString(); _wsh.Cells[24 + j, 5] = dr["Reason"].ToString(); j++; } } } _wsh.Cells[22, 11] = douTotalPrice.ToString(); //保存 string filePath = System.IO.Directory.GetCurrentDirectory() + @"\tempPDF\tempExcel.xls"; app.AlertBeforeOverwriting = false; _wbk.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //退出和释放 _wbk.Close(null, null, null); wbks.Close(); app.Quit(); //释放掉多余的excel进程 System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; return(true); }
/// <summary> /// создание Excel отчета за день/неделю/месяц/квартал /// </summary> /// <param name="values"></param> /// <param name="win"></param> /// <param name="conn"></param> /// <param name="list_day_data"></param> /// <param name="list_week_data"></param> /// <param name="list_month_data"></param> /// <param name="list_quater_data"></param> public async void Create_Excel_Doc(List <Flovers_WPF.Reply_Window.Data> values, Reply_Window win, SQLite.SQLiteAsyncConnection conn, List <Flovers_WPF.Reply_Window.Data> list_day_data, List <Flovers_WPF.Reply_Window.Data> list_week_data, List <Flovers_WPF.Reply_Window.Data> list_month_data, List <Flovers_WPF.Reply_Window.Data> list_quater_data) { Excel.Application exapp = new Excel.Application(); exapp.SheetsInNewWorkbook = 1; exapp.Workbooks.Add(Type.Missing); exapp.DisplayAlerts = true; exapp.Visible = true; Excel.Workbooks exappworkbooks = exapp.Workbooks; Excel.Workbook exappworkbook = exappworkbooks[1]; exappworkbook.Saved = false; Excel.Sheets excellsheets = exappworkbook.Worksheets; Excel.Worksheet excellworksheet = (Excel.Worksheet)excellsheets.get_Item(1); excellworksheet.Activate(); Excel.Range excelcells; for (int j = 1; j < 4; j++) { if (j == 1) { excelcells = (Excel.Range)excellworksheet.Cells[1, j]; excelcells.Value2 = "время/дата"; excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (j == 2) { excelcells = (Excel.Range)excellworksheet.Cells[1, j]; excelcells.Value2 = "Количество"; excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (j == 3) { excelcells = (Excel.Range)excellworksheet.Cells[1, j]; excelcells.Value2 = "Букет"; excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } } if (values != null) { for (int m = 2; m < values.Count + 2; m++) { for (int n = 1; n < 4; n++) { if (n == 1) { excelcells = (Excel.Range)excellworksheet.Cells[m, n]; excelcells.Value2 = values[m - 2].time.ToString(); excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = false; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (n == 2) { excelcells = (Excel.Range)excellworksheet.Cells[m, n]; excelcells.Value2 = values[m - 2].count.ToString(); excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = false; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (n == 3) { Bouquets b = await conn.GetAsync <Bouquets>(values[m - 2].bouqet_id); excelcells = (Excel.Range)excellworksheet.Cells[m, n]; excelcells.Value2 = b.name.ToString(); excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = false; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } } } Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excellworksheet.ChartObjects(Type.Missing); Excel.ChartObject chartsobj = chartsobjrcts.Add(10, 200, 500, 300); Excel.Chart excelchart = chartsobj.Chart; excelcells = excellworksheet.get_Range("A1", "B" + (values.Count + 1).ToString()); excelchart.SetSourceData(excelcells, Type.Missing); excelchart.ChartType = Excel.XlChartType.xlLine; excelchart.HasTitle = true; if (values == list_day_data) { excelchart.ChartTitle.Text = "Продажи за день"; } if (values == list_week_data) { excelchart.ChartTitle.Text = "Продажи за неделю"; } if (values == list_month_data) { excelchart.ChartTitle.Text = "Продажи за месяц"; } if (values == list_quater_data) { excelchart.ChartTitle.Text = "Продажи за квартал"; } excelchart.ChartTitle.Font.Size = 14; excelchart.ChartTitle.Font.Color = 255; excelchart.ChartTitle.Shadow = true; } else { System.Windows.MessageBox.Show("нет данных для отчета"); exapp.Quit(); } }
public void WriteToExcel_hisDetail(DataTable thisTable, string FileName, string sheetName) { string strFilePath = FileName; string XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\历史明细模板.xls"; Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbooks wbks = app.Workbooks; Excel._Workbook _wbk = wbks.Add(XLSName); Excel.Sheets shs = _wbk.Sheets; Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1); try { int sheetRowsCount = _wsh.UsedRange.Rows.Count; int count = thisTable.Columns.Count; //设置列名 //foreach (DataColumn myNewColumn in thisTable.Columns) //{ // _wsh.Cells[0, count] = myNewColumn.ColumnName; // count = count + 1; //}er //for (int i = 0; i < count; i++) //{ // _wsh.Cells[1, i + 1] = thisTable.Columns[i].ColumnName; //} //加入內容 for (int i = 1; i <= thisTable.Rows.Count; i++) { int intBlockRowsNum = 0; for (int j = 1; j <= thisTable.Columns.Count; j++) { int[] intBlockList = { 1, 2, 8, 9, 10, 14, 15, 16, 17, 18, 19 }; if (Array.IndexOf(intBlockList, j) < 0 && j != 7) { _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = thisTable.Rows[i - 1][j - 1]; } else if (j == 7) { switch ((int)thisTable.Rows[i - 1][j - 1]) { case 1: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "人民币"; break; case 2: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "美元"; break; case 3: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "港币"; break; case 4: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "澳元"; break; case 5: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "新元"; break; case 6: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "马币"; break; case 7: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "英镑"; break; case 8: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "欧元"; break; case 9: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "日元"; break; case 10: _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "台币"; break; } } else { intBlockRowsNum++; } } } _wsh.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; _wsh.Cells.Columns.AutoFit(); _wsh.Cells.Rows.AutoFit(); //若為EXCEL2000, 將最後一個參數拿掉即可 _wbk.SaveAs(strFilePath, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null); } catch (Exception ex) { } finally { //關閉文件 _wbk.Close(false, Type.Missing, Type.Missing); app.Workbooks.Close(); app.Quit(); //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wsh); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); _wsh = null; _wbk = null; app = null; } }
public static void ForwardersOnBusinessTrips(List <ClassResource.Forwarder> forwarders, List <ClassResource.Route> routes, List <ClassResource.Destination> destinations, List <ClassResource.Company> companies, List <ClassResource.Request> requests) { Excel.Application excelapp = new Excel.Application() { Visible = true }; Excel.Range excelcells; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(Type.Missing); Excel.Sheets excelsheets = excelapp.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelcells = excelworksheet.get_Range("A1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Экспедитор"; excelcells = excelworksheet.get_Range("B1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Дата убытия"; excelcells = excelworksheet.get_Range("C1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Пункт назначения"; excelcells = excelworksheet.get_Range("D1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Товар"; int rowCount = 2; foreach (var forwarder in forwarders.FindAll(x => routes.Find(y => y.RouteStatus == "Закрыт" && y.IDForwarder == x.ID) != null)) { excelcells = excelworksheet.get_Range("A" + rowCount.ToString(), Type.Missing); excelcells.Value2 = forwarder.Name; excelcells = excelworksheet.get_Range("B" + rowCount.ToString(), Type.Missing); excelcells.Value2 = routes.Find(y => y.RouteStatus == "Закрыт" && y.IDForwarder == forwarder.ID).DepartureDate; List <ClassResource.Destination> temp = destinations.FindAll(x => x.IDRoute == routes.Find(y => y.RouteStatus == "Закрыт" && y.IDForwarder == forwarder.ID).ID); temp.Sort((a, b) => (a.Number.CompareTo(b.Number))); for (int i = 0; i < temp.Count; i++) { if (i != 0) { if (companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i - 1].IDRequest).IDCompany).City != companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).City) { excelcells = excelworksheet.get_Range("C" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).City; } } else { excelcells = excelworksheet.get_Range("C" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).City; } excelcells = excelworksheet.get_Range("D" + rowCount.ToString(), Type.Missing); excelcells.Value2 = requests.Find(y => y.ID == temp[i].IDRequest).ProductName; rowCount++; } } excelcells = (Excel.Range)excelworksheet.Columns["A:A", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["B:B", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["C:C", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["D:D", Type.Missing]; excelcells.Columns.AutoFit(); }
public List<AccountExcel> readExcel(string sExcelPath) { var lReturn = new List<AccountExcel>(); string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2 { if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "") { var adAccount = new AccountExcel(); adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value); adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value); adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value); adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value); adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value); adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value); adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value); adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value); adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value); adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value); adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value); adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value); adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value); lReturn.Add(adAccount); } } //Close the Excel Object objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); objBooks.Close(); objExcelApp.Quit(); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objSheets); Marshal.ReleaseComObject(objBooks); Marshal.ReleaseComObject(objBook); Marshal.ReleaseComObject(objExcelApp); objSheet = null; objSheets = null; objBooks = null; objBook = null; objExcelApp = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); return (lReturn); }
public static void TotalReport(List <ClassResource.Forwarder> forwarders, List <ClassResource.Route> routes, List <ClassResource.Destination> destinations, List <ClassResource.Company> companies, List <ClassResource.Request> requests) { Excel.Application excelapp = new Excel.Application() { Visible = true }; Excel.Range excelcells; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(Type.Missing); Excel.Sheets excelsheets = excelapp.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelcells = excelworksheet.get_Range("A1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Экспедитор"; excelcells = excelworksheet.get_Range("B1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Дата убытия"; excelcells = excelworksheet.get_Range("C1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Дата возвращения"; excelcells = excelworksheet.get_Range("D1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Пункт назначения"; excelcells = excelworksheet.get_Range("E1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Фирма"; excelcells = excelworksheet.get_Range("F1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Адрес"; excelcells = excelworksheet.get_Range("G1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Дата прибытия"; excelcells = excelworksheet.get_Range("H1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Наименование товара"; excelcells = excelworksheet.get_Range("I1", Type.Missing); excelcells.EntireRow.Font.Bold = true; excelcells.Value2 = "Количество"; int rowCount = 2; foreach (var forwarder in forwarders.FindAll(x => routes.Find(y => y.RouteStatus != "Открыт" && y.IDForwarder == x.ID) != null)) { excelcells = excelworksheet.get_Range("A" + rowCount.ToString(), Type.Missing); excelcells.Value2 = forwarder.Name; foreach (var route in routes.FindAll(y => y.RouteStatus != "Открыт" && y.IDForwarder == forwarder.ID)) { excelcells = excelworksheet.get_Range("B" + rowCount.ToString(), Type.Missing); excelcells.Value2 = route.DepartureDate; excelcells = excelworksheet.get_Range("C" + rowCount.ToString(), Type.Missing); excelcells.Value2 = route.ReturnDate; List <ClassResource.Destination> temp = destinations.FindAll(x => x.IDRoute == route.ID); temp.Sort((a, b) => (a.Number.CompareTo(b.Number))); for (int i = 0; i < temp.Count; i++) { if (i != 0) { if (companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i - 1].IDRequest).IDCompany).City != companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).City) { excelcells = excelworksheet.get_Range("D" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).City; excelcells = excelworksheet.get_Range("E" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).Name; excelcells = excelworksheet.get_Range("F" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).Address; } } else { excelcells = excelworksheet.get_Range("D" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).City; excelcells = excelworksheet.get_Range("E" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).Name; excelcells = excelworksheet.get_Range("F" + rowCount.ToString(), Type.Missing); excelcells.Value2 = companies.Find(x => x.ID == requests.Find(y => y.ID == temp[i].IDRequest).IDCompany).Address; } excelcells = excelworksheet.get_Range("G" + rowCount.ToString(), Type.Missing); excelcells.Value2 = temp[i].ArrivalDate; excelcells = excelworksheet.get_Range("H" + rowCount.ToString(), Type.Missing); excelcells.Value2 = requests.Find(y => y.ID == temp[i].IDRequest).ProductName; excelcells = excelworksheet.get_Range("I" + rowCount.ToString(), Type.Missing); excelcells.Value2 = requests.Find(y => y.ID == temp[i].IDRequest).Quantity; rowCount++; } } } excelcells = (Excel.Range)excelworksheet.Columns["A:A", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["B:B", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["C:C", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["D:D", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["E:E", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["F:F", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["G:G", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["H:H", Type.Missing]; excelcells.Columns.AutoFit(); excelcells = (Excel.Range)excelworksheet.Columns["I:I", Type.Missing]; excelcells.Columns.AutoFit(); }
public void edit() { loadvalues(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = true; string workbookPath = "C:\\Sisu_Nipunatha\\results.xlsx"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Sheet1"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); int first_cell = cells[Convert.ToInt32(competition_id)]; excelWorksheet.Cells[first_cell, 3] = dtforID.Rows[0][0].ToString(); excelWorksheet.Cells[first_cell + 1, 3] = dtforID.Rows[1][0].ToString(); excelWorksheet.Cells[first_cell + 2, 3] = dtforID.Rows[2][0].ToString(); excelWorksheet.Cells[first_cell + 3, 3] = dtforID.Rows[3][0].ToString(); excelWorksheet.Cells[first_cell + 4, 3] = dtforID.Rows[4][0].ToString(); excelWorksheet.Cells[first_cell, 4] = dtforID.Rows[0][1].ToString(); excelWorksheet.Cells[first_cell + 1, 4] = dtforID.Rows[1][1].ToString(); excelWorksheet.Cells[first_cell + 2, 4] = dtforID.Rows[2][1].ToString(); excelWorksheet.Cells[first_cell + 3, 4] = dtforID.Rows[3][1].ToString(); excelWorksheet.Cells[first_cell + 4, 4] = dtforID.Rows[4][1].ToString(); excelWorksheet.Cells[first_cell, 5] = dtforID.Rows[0][2].ToString(); excelWorksheet.Cells[first_cell + 1, 5] = dtforID.Rows[1][2].ToString(); excelWorksheet.Cells[first_cell + 2, 5] = dtforID.Rows[2][2].ToString(); excelWorksheet.Cells[first_cell + 3, 5] = dtforID.Rows[3][2].ToString(); excelWorksheet.Cells[first_cell + 4, 5] = dtforID.Rows[4][2].ToString(); excelWorkbook.Save(); excelWorkbook.Close(); excelApp.Quit(); }
public static void ExportDataGridViewTo_Excel12(DataGridView myDataGridView) { try { Excel_12.Application oExcel_12 = null; //Excel_12 Application Excel_12.Workbook oBook = null; // Excel_12 Workbook Excel_12.Sheets oSheetsColl = null; // Excel_12 Worksheets collection Excel_12.Worksheet oSheet = null; // Excel_12 Worksheet Excel_12.Range oRange = null; // Cell or Range in worksheet Object oMissing = System.Reflection.Missing.Value; // Create an instance of Excel_12. oExcel_12 = new Excel_12.Application(); // Make Excel_12 visible to the user. oExcel_12.Visible = true; // Set the UserControl property so Excel_12 won't shut down. oExcel_12.UserControl = true; // System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US"); // Add a workbook. oBook = oExcel_12.Workbooks.Add(oMissing); // Get worksheets collection oSheetsColl = oExcel_12.Worksheets; // Get Worksheet "Sheet1" oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1"); // Export titles for (int j = 0; j < myDataGridView.Columns.Count; j++) { oRange = (Excel_12.Range)oSheet.Cells[1, j + 1]; oRange.Value2 = myDataGridView.Columns[j].HeaderText; } // Export data for (int i = 0; i < myDataGridView.Rows.Count; i++) { for (int j = 0; j < myDataGridView.Columns.Count; j++) { oRange = (Excel_12.Range)oSheet.Cells[i + 2, j + 1]; oRange.Value2 = myDataGridView[j, i].Value.ToString(); } } // Release the variables. //oBook.Close(false, oMissing, oMissing); oBook = null; //oExcel_12.Quit(); oExcel_12 = null; // Collect garbage. GC.Collect(); } catch { MessageBox.Show("CAN NOT GENERATE EXCEL FILE", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }
/// <summary> /// 创建一个Excel实例 /// </summary> ///// <param name="strTitle">表头,需与查询sql语句对齐一致。</param> ///// <param name="strSql">查询的sql语句,表头的文字需与该sql语句对齐一致。</param> ///// <param name="strTableName">查询的表名</param> ///// <param name="strMastTitle">标题</param> ///// <param name="TableIDName">主键</param> ///// <param name="sqlwhere">查询条件包含[where]</param> //public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle, string TableIDName, string sqlwhere) public void DeclareExcelApp() { // 创建一个Excel实例 objExcel = new Excel.ApplicationClass(); objExcel.Visible = false; objBooks = (Excel.Workbooks)objExcel.Workbooks; objBook = (Excel.Workbook)(objBooks.Add(missing)); objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.ActiveSheet; m_objSheets = (Excel.Sheets)objBook.Worksheets; if (strFileName == "") { MessageBox.Show("文件路径不能为空"); return; } //string strFileName = SaveExcelApp(strMastTitle);//获取文件存放路径 intSheetTotalSize = GetTotalSize(strTableName, sqlwhere1);//获取分成几个SHEET try { if (intSheetTotalSize <= sheetcount) { if (intSheetTotalSize <= 3) { if (this.dbTotalSize <= this.dbSheetSize) { this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); } else if (this.dbTotalSize <= this.dbSheetSize * 2) { this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); } else { this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); this.ExportDataByQueryTable(2, false, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); } } for (int i = 3; i < intSheetTotalSize; i++) { m_objSheets.Add(missing, m_objSheets.get_Item(i), missing, missing); } this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); for (int i = 2; i <= m_objSheets.Count; i++) { this.ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle, TableIDName, sqlwhere, strFileName); } //保存excel文件在服务器 //关闭Microsoft.Office.Interop.Excel objBook.SaveAs(strFileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); objBook.Close(false, missing, missing); objBooks.Close(); objExcel.Quit(); MessageBox.Show("Excel成功导出"); } else { MessageBox.Show("导出的数据过大请重新设置查询条件"); } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } finally { //释放资源 if (!objSheet.Equals(null)) { System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet); } if (objBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook); } if (objBooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks); } if (objExcel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel); } GC.Collect(); KillProcess("EXCEL"); } }
/// <summary> /// 持续打开Excel文件 /// </summary> /// <returns></returns> public bool OpenFileContinuously() { try { app = new Microsoft.Office.Interop.Excel.Application(); workbook = app.Workbooks.Open(FileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); sheets = workbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(SheetNumber);//读取一张表 iRowCount = worksheet.UsedRange.Rows.Count; iColumnCount = worksheet.UsedRange.Columns.Count; booFileOpenState = true; return true; } catch (Exception ex) { strErrorString = ex.Message; return false; } }
/// <param name="strSql"></param> /// <summary> /// 使用QueryTable从一个外部数据源创建Worksheet /// </summary> /// <param name="intSheetNumber">导出第几个sheet</param> /// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param> /// <param name="strTitle">表头,需与查询sql语句对齐一致。</param> /// <param name="strSql">查询的sql语句,表头的文字需与该sql语句对齐一致。</param> /// <param name="strTablName">查询的表名</param> /// <param name="strMastTitle">标题</param> /// <param name="TableIDName">主键</param> /// <param name="sqlwhere">查询条件包含[where]</param> /// <param name="strFileName">Excel文件导出路径</param> public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan, string[] strTitle, string strSql, string strTablName, string strMastTitle, string TableIDName, string sqlwhere, string strFileName) { try { string strQuery = string.Empty; string sqlwhere1 = sqlwhere != "" ? "" : sqlwhere.Substring(7, sqlwhere.Length - 7); if (blIsMoreThan) { strQuery = String.Format("select top {0} from {1} where not {2} in (select top {3} from {4} {5}) {6}", dbSheetSize + " " + strSql, strTablName, TableIDName, dbSheetSize * (intSheetNumber - 1) + TableIDName, strTablName, sqlwhere, sqlwhere1); } else { strQuery = String.Format("Select Top {0} from {1} {2}", dbSheetSize + " " + strSql, strTablName, sqlwhere); } int strTitleNnamecount = strTitle.Length; m_objSheet = (Excel.Worksheet)(m_objSheets.get_Item(intSheetNumber));//操作哪个SHEET //m_objSheet.Name = strMastTitle + intSheetNumber.ToString() + DateTime.Now.ToShortDateString(); //sheet名称 m_objSheet.Cells[1, 1] = strMastTitle;//标题 m_objSheet.Cells[2, 1] = "打印日期" + DateTime.Now.ToShortDateString(); //写入标题 for (int i = 1; i <= strTitleNnamecount; i++) { m_objSheet.Cells[3, i] = strTitle[i - 1].ToString(); } m_objRange = m_objSheet.get_Range("A4", missing);//从第四行开始写入 //格式设置 m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, strTitleNnamecount]).MergeCells = true; //合并单元格 m_objSheet.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[2, strTitleNnamecount]).MergeCells = true; //合并单元格 //标题设置 Excel.Range m_objRangeMastTitle = m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, strTitleNnamecount]); m_objRangeMastTitle.Font.Name = "黑体"; //设置字体 m_objRangeMastTitle.Font.Size = 16; //设置字体大小 m_objRangeMastTitle.Font.Bold = true; //字体加粗 m_objRangeMastTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中 m_objRangeMastTitle.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中 //标题设置 Excel.Range m_objRangeTitle = m_objSheet.get_Range(m_objSheet.Cells[3, 1], m_objSheet.Cells[3, strTitleNnamecount]); m_objRangeTitle.Font.Name = "黑体"; //设置字体 m_objRangeTitle.Font.Size = 12; //设置字体大小 m_objRangeTitle.Font.Bold = true; //字体加粗 m_objRangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中 m_objRangeTitle.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中 //参数依次为:数据连接,填充起始单元格,查询SQL语句 tb = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + _connectionString, m_objRange, strQuery); tb.Refresh(tb.BackgroundQuery);//是否异步查询 //区域删除【第4行】 //Excel.Range range = objExcel.get_Range(objExcel.Cells[4, strTitleNnamecount], objExcel.Cells[4, strTitleNnamecount]); //range.Select(); //if (true)//是否整行删除 m_objRange.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp); //else // range.Delete(XlDeleteShiftDirection.xlShiftUp); } catch { } }
public Form1() { InitializeComponent(); double x, y, d, dx, dy, x0, y0; int y_step; excelapp = new Excel.Application(); excelapp.Visible = true; excelapp.SheetsInNewWorkbook = 3; excelapp.Workbooks.Add(Type.Missing); excelapp.DisplayAlerts = true; excelappworkbooks = excelapp.Workbooks; excelappworkbook = excelappworkbooks[1]; excelsheets = excelappworkbook.Worksheets; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelcells_a1 = excelworksheet.get_Range("A1", Type.Missing); excelcells = excelcells_a1.get_Offset(0, 0); /////////////////////////// ////// Источники /////// /////////////////////////// double sourceRadius = 0.2; sources[0].id = 0; sources[0].x = 0.27; sources[0].y = 2.00; sources[0].radius = sourceRadius; sources[1].id = 1; sources[1].x = 1.10; sources[1].y = 2.81; sources[1].radius = sourceRadius; sources[2].id = 2; sources[2].x = 2.22; sources[2].y = 2.64; sources[2].radius = sourceRadius; sources[3].id = 3; sources[3].x = 2.80; sources[3].y = 1.61; sources[3].radius = sourceRadius; sources[4].id = 4; sources[4].x = 2.39; sources[4].y = 0.51; sources[4].radius = sourceRadius; sources[5].id = 5; sources[5].x = 1.30; sources[5].y = 0.13; sources[5].radius = sourceRadius; sources[6].id = 6; sources[6].x = 0.36; sources[6].y = 0.81; sources[6].radius = sourceRadius; sources[7].id = 7; sources[7].x = 1.49; sources[7].y = 1.50; sources[7].radius = sourceRadius; /////////////////////////// ////// Приёмники /////// /////////////////////////// /* // Вариант один к одному для ВОСЬМИ приёмников double receiverRadius = 0.23; double receiverInnerRadius = 0.13; receiversCount = 8; receivers[0].id = 0; receivers[0].x = 0.23; receivers[0].y = 1.97; receivers[0].radius = receiverRadius; receivers[0].innerRadius = receiverInnerRadius; receivers[1].id = 1; receivers[1].x = 1.08; receivers[1].y = 2.76; receivers[1].radius = receiverRadius; receivers[1].innerRadius = receiverInnerRadius; receivers[2].id = 2; receivers[2].x = 2.23; receivers[2].y = 2.62; receivers[2].radius = receiverRadius; receivers[2].innerRadius = receiverInnerRadius; receivers[3].id = 3; receivers[3].x = 2.82; receivers[3].y = 1.63; receivers[3].radius = receiverRadius; receivers[3].innerRadius = receiverInnerRadius; receivers[4].id = 4; receivers[4].x = 2.44; receivers[4].y = 0.54; receivers[4].radius = receiverRadius; receivers[4].innerRadius = receiverInnerRadius; receivers[5].id = 5; receivers[5].x = 1.31; receivers[5].y = 0.2; receivers[5].radius = receiverRadius; receivers[5].innerRadius = receiverInnerRadius; receivers[6].id = 6; receivers[6].x = 0.35; receivers[6].y = 0.81; receivers[6].radius = receiverRadius; receivers[6].innerRadius = receiverInnerRadius; receivers[7].id = 7; receivers[7].x = 1.49; receivers[7].y = 1.50; receivers[7].radius = receiverRadius; receivers[7].innerRadius = receiverInnerRadius; */ // Полтное гексагональное покрытие приёмниками x0 = 1.5; // Центр шины - x y0 = 1.5; // Центр шины - y /* dx = 0.46; // Шаг по x для гексагональной разметки dy = 0.42; // Шаг по y для гексагональной разметки double receiverRadius = 0.23; double receiverInnerRadius = 0.0; */ dx = 0.46; // Шаг по x для гексагональной разметки dy = 0.42; // Шаг по y для гексагональной разметки double receiverRadius = 0.20; double receiverInnerRadius = 0.10; y_step = 0; y = 0; receiversCount = 0; while (y < busRadius * 2) { // Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному if (y_step % 2 == 0) { x = 0; } else { x = dx / 2; } while (x < busRadius * 2) { d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y)); if (d < busRadius) { // Канал в шине (точка в круге) receivers[receiversCount].x = x; receivers[receiversCount].y = y; receivers[receiversCount].radius = receiverRadius; receivers[receiversCount].innerRadius = receiverInnerRadius; receiversCount++; } x = x + dx; } y = y + dy; y_step++; } //////////////////////// ////// Каналы /////// //////////////////////// dx = 0.0429; // Шаг по x для гексагональной разметки dy = 0.0357; // Шаг по y для гексагональной разметки x0 = 1.5; // Центр шины - x y0 = 1.5; // Центр шины - y double channelRaduis = 0.021; // Радиус одного канала y_step = 0; y = 0; while (y < busRadius * 2) { // Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному if (y_step % 2 == 0) { x = 0; } else { x = dx / 2; } while (x < busRadius * 2) { d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y)); if (d < busRadius) { // Канал в шине (точка в круге) channels[channelsCount].x = x; channels[channelsCount].y = y; channels[channelsCount].radius = channelRaduis; channels[channelsCount].isOk = true; channelsCount++; } x = x + dx; } y = y + dy; y_step++; } }
/// <summary> /// 创建一个Excel程序实例 /// </summary> private void CreateExcelRef() { _excelApp = new Excel.Application(); _books = (Excel.Workbooks)_excelApp.Workbooks; _book = (Excel._Workbook)(_books.Add(_optionalValue)); _sheets = (Excel.Sheets)_book.Worksheets; _sheet = (Excel._Worksheet)(_sheets.get_Item(1)); }
private void forotborisfailov(string[] vsS, int a1, ref int z1, ref int z2, ref int z3, ref string s, string cilka) { excelapp = new Excel.Application(); excelappworkbooks = excelapp.Workbooks; excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, cilka), 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); excelsheets = excelappworkbook.Worksheets; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); for (int i = 0; i < 350; i++) { if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString(); else { z3++; if (z3 == 1) z1 = 0; s = vsS[z2] + vsS[z1] + a1.ToString(); if (z1 == 25) { z2++; z1 = 0; } } if (s == "IW" + a1.ToString()) break; excelcells = excelworksheet.get_Range(s, Type.Missing); string excelnumber = excelcells.Value2; if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber); z1++; } z1 = 0; z2 = 0; z3 = 0; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2); for (int i = 0; i < 350; i++) { if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString(); else { z3++; if (z3 == 1) z1 = 0; s = vsS[z2] + vsS[z1] + a1.ToString(); if (z1 == 25) { z2++; z1 = 0; } } if (s == "IW" + a1.ToString()) break; excelcells = excelworksheet.get_Range(s, Type.Missing); string excelnumber = excelcells.Value2; if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber); z1++; } z1 = 0; z2 = 0; z3 = 0; excelappworkbook.Close(); excelappworkbooks.Close(); excelapp.Workbooks.Close(); }
public void UcitavanjeFaktura() { try { // umetanje vrijednosti ExcelObj = new Microsoft.Office.Interop.Excel.Application(); excelApp = new Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); //cijena for (int red = 26; red <= 31; red++) { var traži = (worksheet.Cells[red, 6] as Microsoft.Office.Interop.Excel.Range).Value; string STRtraži = Convert.ToString(traži); string n = STRtraži; float val; if (((float.TryParse(n, out val))) && STRtraži.Length != 0) { File.WriteAllText(Application.StartupPath + "\\Cijena", STRtraži); //rabat i PDV try { var te = (worksheet.Cells[red, 10] as Microsoft.Office.Interop.Excel.Range).Value; STRte = Convert.ToString(te); if (STRte.Length == 0) { var rabata = (worksheet.Cells[red, 9] as Microsoft.Office.Interop.Excel.Range).Value; string STRrabata = Convert.ToString(rabata); } } catch { var rabata = (worksheet.Cells[red, 9] as Microsoft.Office.Interop.Excel.Range).Value; string STRrabata = Convert.ToString(rabata); File.WriteAllText(Application.StartupPath + "\\PDV", STRrabata); goto dalje; } var rabat = (worksheet.Cells[red, 9] as Microsoft.Office.Interop.Excel.Range).Value; string STRrabat = Convert.ToString(rabat); File.WriteAllText(Application.StartupPath + "\\rabat", STRrabat); var PDV = (worksheet.Cells[red, 10] as Microsoft.Office.Interop.Excel.Range).Value; string STRPDV = Convert.ToString(PDV); File.WriteAllText(Application.StartupPath + "\\PDV", STRPDV); dalje: break; } } //pozicija for (int stupac = 1; stupac <= 5; stupac++) { for (int red = 26; red <= 34; red++) { var trazi = (worksheet.Cells[red, stupac] as Microsoft.Office.Interop.Excel.Range).Value; string STRtrazi = Convert.ToString(trazi); if (STRtrazi != null) { if ((STRtrazi.Contains("Pozicija:") || STRtrazi.Contains("POZICIJA:")) && STRtrazi.Length != 0) { var trazi1 = (worksheet.Cells[red, stupac + 1] as Microsoft.Office.Interop.Excel.Range).Value; string STRtrazi1 = Convert.ToString(trazi1); if (STRtrazi1 == null) File.WriteAllText(Application.StartupPath + "\\Pozicija", STRtrazi); else File.WriteAllText(Application.StartupPath + "\\Pozicija", STRtrazi1); break; } } } } //relacija for (int stupac = 1; stupac <= 3; stupac++) { for (int red = 26; red <= 31; red++) { var traži = (worksheet.Cells[red, stupac] as Microsoft.Office.Interop.Excel.Range).Value; STRtražiRelaciju = Convert.ToString(traži); if (STRtražiRelaciju != null) { if (STRtražiRelaciju == ImeRelacije || STRtražiRelaciju.Contains("-")) { //Ne može prenašati vrijednosti u aktivni form File.Delete(Application.StartupPath + "\\RelacijaIme"); File.WriteAllText(Application.StartupPath + "\\StupacRelacija", stupac.ToString()); File.WriteAllText(Application.StartupPath + "\\Relacija", STRtražiRelaciju); File.WriteAllText(Application.StartupPath + "\\RedRelacija", red.ToString()); provjeraRelacija++; goto kraj; } } } } if(provjeraRelacija == 0 ) { MessageBox.Show("Došlo je do otvaranja neočekivane fakture,program će se resetirati."); File.AppendAllText(Application.StartupPath + "\\Fakture\\FaktureNepodobneZaUcitavanje", Environment.NewLine + OtvorenaFaktura); provjeraRelacija++; Zatvaranje(); Application.Restart(); } kraj: this.Refresh(); } catch(Exception ex) { MessageBox.Show(ex.ToString()); if (provjeraRelacija < 2) { MessageBox.Show("Došlo je do otvaranja neočekivane fakture,program će se resetirati."); File.AppendAllText(Application.StartupPath + "\\Fakture\\FaktureNepodobneZaUcitavanje", Environment.NewLine + OtvorenaFaktura); Zatvaranje(); Application.Restart(); } } }
private void btnImprimir_Click(object sender, EventArgs e) { try { DialogResult dr = MessageBox.Show("¿Está seguro que quiere imprimir el estudio?", "Consulta", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dr == DialogResult.Yes) { Cursor.Current = Cursors.WaitCursor; // Ruta de xls string fuentePath = ConfigurationManager.AppSettings["dirFormato"]; string destinoPath = ConfigurationManager.AppSettings["dirSalidas"]; string nombreArchivo = ConfigurationManager.AppSettings["formato"]; string nuevoArchivo = nombreArchivo + "_" + dat.IdPersona + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss"); string ext = ".xlsx"; string current = Environment.CurrentDirectory; // Use Path class to manipulate file and directory paths. string workbookPath = System.IO.Path.Combine(fuentePath, nombreArchivo + ext); string destinoFile = System.IO.Path.Combine(destinoPath, nuevoArchivo + ext); Excel.Application excelApp = new Excel.Application(); excelApp.Visible = false; // open an existing workbook //string workbookPath = @"C:\Temp\ecov.xlsx"; Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // get all sheets in workbook Excel.Sheets excelSheets = excelWorkbook.Worksheets; // get some sheet string currentSheet = "Estudio de caso"; Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet); DatosNotificante notificante; DatosGenerales generales; Antecedentes antecedentes; IEnumerable <Viaje> viajes; DatosClinicos clinicos; DatosUnidadMedica unidadMedica; Tratamiento tratamiento; Laboratorio laboratorio; Evolucion evolucion; IEnumerable <Contactos> contactos; using (var context = new EstudioEpidemiologicoEntities()) { notificante = context.DatosNotificante.Where(l => l.IdUnidadNotificante == 1).ToList().First(); generales = context.DatosGenerales.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); antecedentes = context.Antecedentes.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); viajes = context.Viaje.Where(l => l.IdPersona == dat.IdPersona).ToList(); clinicos = context.DatosClinicos.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); unidadMedica = context.DatosUnidadMedica.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); tratamiento = context.Tratamiento.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); laboratorio = context.Laboratorio.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); evolucion = context.Evolucion.Where(l => l.IdPersona == dat.IdPersona).ToList().First(); contactos = context.Contactos.Where(l => l.IdPersona == dat.IdPersona).ToList(); } //DATOS DE LA UNIDAD NOTIFICANTE excelWorksheet.Range["C7"].Value = notificante.Entidad; excelWorksheet.Range["F7"].Value = notificante.Jurisdiccion; excelWorksheet.Range["I7"].Value = notificante.Municipio; excelWorksheet.Range["P7"].Value = notificante.NombreUnidadMedica; excelWorksheet.Range["C9"].Value = notificante.Institucion; excelWorksheet.Range["I9"].Value = notificante.CLUES; //DATOS GENERALES excelWorksheet.Range["E11"].Value = generales.FechaIngresoUnidad; excelWorksheet.Range["E14"].Value = generales.PrimerApellido; excelWorksheet.Range["k14"].Value = generales.SegundoApellido; excelWorksheet.Range["Q14"].Value = generales.Nombres; excelWorksheet.Range["E16"].Value = generales.NacimientoDía; excelWorksheet.Range["G16"].Value = generales.NacimientoMes; excelWorksheet.Range["I16"].Value = generales.NacimientoAnio; excelWorksheet.Range["K16"].Value = generales.Edad; excelWorksheet.Range["N16"].Value = generales.CURP; if (generales.Sexo.Equals("H")) { excelWorksheet.Range["D18"].Value = "X"; } else { excelWorksheet.Range["D19"].Value = "X"; } if (!generales.Embarazada.Equals(null)) { if (generales.Embarazada.Value) { excelWorksheet.Range["G19"].Value = "X"; } else { excelWorksheet.Range["H19"].Value = "X"; } } excelWorksheet.Range["K19"].Value = generales.MesesEmbarazo; if (!generales.EnPeriodoPuerperio.Equals(null)) { if (generales.EnPeriodoPuerperio.Value) { excelWorksheet.Range["P19"].Value = "X"; } else { excelWorksheet.Range["Q19"].Value = "X"; } } excelWorksheet.Range["S19"].Value = generales.DiasPuerperio; //Nacionalidad if (generales.Nacionalidad.Equals("M")) { excelWorksheet.Range["D21"].Value = "X"; } else { excelWorksheet.Range["F21"].Value = "X"; } excelWorksheet.Range["J21"].Value = generales.PaisOrigen; excelWorksheet.Range["R21"].Value = generales.NoExpedienteSeguridadSocial; excelWorksheet.Range["E23"].Value = generales.EntidadNacimiento; excelWorksheet.Range["O23"].Value = generales.EntidadDelegacionResidencia; excelWorksheet.Range["E25"].Value = generales.MunicipioResidencia; excelWorksheet.Range["M25"].Value = generales.Localidad; excelWorksheet.Range["E27"].Value = generales.Calle; excelWorksheet.Range["M27"].Value = generales.NoExterno; excelWorksheet.Range["R27"].Value = generales.NoInterno; excelWorksheet.Range["E29"].Value = generales.EntreCalles; excelWorksheet.Range["N29"].Value = generales.YCalle; excelWorksheet.Range["E31"].Value = generales.Colonia; excelWorksheet.Range["J31"].Value = generales.CP; excelWorksheet.Range["P31"].Value = generales.Telefono; //SeReconoceIndigena if (!generales.SeReconoceIndigena.Equals(null)) { if (generales.SeReconoceIndigena) { excelWorksheet.Range["F33"].Value = "X"; } else { excelWorksheet.Range["G33"].Value = "X"; } } //HablaAlgunaLenguaIndigena if (!generales.HablaAlgunaLenguaIndigena.Equals(null)) { if (generales.HablaAlgunaLenguaIndigena) { excelWorksheet.Range["N33"].Value = "X"; } else { excelWorksheet.Range["O33"].Value = "X"; } } excelWorksheet.Range["D36"].Value = generales.Ocupacion; //EsMigrante if (!generales.EsMigrante.Equals(null)) { if (generales.EsMigrante) { excelWorksheet.Range["N36"].Value = "X"; } else { excelWorksheet.Range["O36"].Value = "X"; } } ///ANTECEDENTES EPIDEMIOLÓGICOS if (!antecedentes.ViajoChina14dPrevInicioSintomas.Equals(null)) { if (antecedentes.ViajoChina14dPrevInicioSintomas) { excelWorksheet.Range["J39"].Value = "X"; } else { excelWorksheet.Range["K39"].Value = "X"; } } if (!antecedentes.ResidenteChina.Equals(null)) { if (antecedentes.ResidenteChina) { excelWorksheet.Range["R39"].Value = "X"; } else { excelWorksheet.Range["S39"].Value = "X"; } } excelWorksheet.Range["F43"].Value = antecedentes.FechaViajeChina; excelWorksheet.Range["M43"].Value = antecedentes.AerolineaVueloLlegadaMexico; excelWorksheet.Range["E46"].Value = antecedentes.FechaLlegadaMexico; if (!antecedentes.ContactoPersonaSintomasRespiratorios.Equals(null)) { if (antecedentes.ContactoPersonaSintomasRespiratorios.Equals("S")) { excelWorksheet.Range["I49"].Value = "X"; } else if (antecedentes.ContactoPersonaSintomasRespiratorios.Equals("N")) { excelWorksheet.Range["J49"].Value = "X"; } else { excelWorksheet.Range["K49"].Value = "X"; } } excelWorksheet.Range["G52"].Value = antecedentes.LugarContactoPersonas; if (!antecedentes.ContactoAnimales.Equals(null)) { if (antecedentes.ContactoAnimales) { excelWorksheet.Range["F55"].Value = "X"; } else { excelWorksheet.Range["G55"].Value = "X"; } } excelWorksheet.Range["J55"].Value = antecedentes.CualAnimal; //Validar marcado excelWorksheet.Range["C58"].Value = antecedentes.TipoContactoAnimal; if (!antecedentes.VisitoMercadoVentaAnimales.Equals(null)) { if (antecedentes.VisitoMercadoVentaAnimales) { excelWorksheet.Range["H60"].Value = "X"; } else { excelWorksheet.Range["I60"].Value = "X"; } } excelWorksheet.Range["K60"].Value = antecedentes.LugarMercadoVentaAnimales; excelWorksheet.Range["Q60"].Value = antecedentes.FechaVisitaMercado; if (!antecedentes.Ult14dContacCercanoPersSospCoV.Equals(null)) { if (antecedentes.Ult14dContacCercanoPersSospCoV) { excelWorksheet.Range["P71"].Value = "X"; } else { excelWorksheet.Range["R71"].Value = "X"; } } if (!antecedentes.Ult14dContacCercanoPersCorfiLabCoV.Equals(null)) { if (antecedentes.Ult14dContacCercanoPersCorfiLabCoV) { excelWorksheet.Range["P73"].Value = "X"; } else { excelWorksheet.Range["R73"].Value = "X"; } } if (!antecedentes.ContacPersViajChina14dPrevIniSintomas.Equals(null)) { if (antecedentes.ContacPersViajChina14dPrevIniSintomas) { excelWorksheet.Range["M76"].Value = "X"; } else { excelWorksheet.Range["N76"].Value = "X"; } } //VIAJES int numCeldaViaje = 65; foreach (Viaje viaje in viajes) { excelWorksheet.Range["F" + numCeldaViaje].Value = viaje.Pais; excelWorksheet.Range["H" + numCeldaViaje].Value = viaje.Ciudad; excelWorksheet.Range["K" + numCeldaViaje].Value = viaje.FechaLlegada; excelWorksheet.Range["O" + numCeldaViaje].Value = viaje.FechaSalida; excelWorksheet.Range["R" + numCeldaViaje].Value = viaje.AerolineaVuelo; numCeldaViaje++; } //DATOS CLINICOS excelWorksheet.Range["E81"].Value = clinicos.FechaInicioSintomas; writeBool(excelWorksheet, "D85", "E85", clinicos.Fiebre); writeBool(excelWorksheet, "D86", "E86", clinicos.Tos); writeBool(excelWorksheet, "D87", "E87", clinicos.DolorToracico); writeBool(excelWorksheet, "D88", "E88", clinicos.DificultadRespiratoria); writeBool(excelWorksheet, "K85", "L85", clinicos.Cefalea); writeBool(excelWorksheet, "K86", "L86", clinicos.Irritabilidad); writeBool(excelWorksheet, "K87", "L87", clinicos.Diarrea); writeBool(excelWorksheet, "K88", "L88", clinicos.Vomito); writeBool(excelWorksheet, "K89", "L89", clinicos.Calosfrios); writeBool(excelWorksheet, "K90", "L90", clinicos.DolorAbdominal); writeBool(excelWorksheet, "K91", "L91", clinicos.Mialgias); writeBool(excelWorksheet, "K92", "L92", clinicos.Artralgias); writeBool(excelWorksheet, "K93", "L93", clinicos.AtaqueAlEdoGral); writeBool(excelWorksheet, "K94", "L94", clinicos.Rinorrea); writeBool(excelWorksheet, "K95", "L95", clinicos.Polipnea); writeBool(excelWorksheet, "K96", "L96", clinicos.Odinofagia); writeBool(excelWorksheet, "K97", "L97", clinicos.Conjuntivitis); writeBool(excelWorksheet, "K98", "L98", clinicos.Cianosis); writeBool(excelWorksheet, "K99", "L99", clinicos.Convulsiones); excelWorksheet.Range["K100"].Value = clinicos.OtroSintoma; writeBool(excelWorksheet, "R83", "S83", clinicos.Diabetes); writeBool(excelWorksheet, "R84", "S84", clinicos.EPOC); writeBool(excelWorksheet, "R85", "S85", clinicos.Asma); writeBool(excelWorksheet, "R86", "S86", clinicos.Inmunosupresion); writeBool(excelWorksheet, "R87", "S87", clinicos.Hipertension); writeBool(excelWorksheet, "R88", "S88", clinicos.VihSida); writeBool(excelWorksheet, "R89", "S89", clinicos.EnfermedadCardio); writeBool(excelWorksheet, "R90", "S90", clinicos.Obesidad); writeBool(excelWorksheet, "R91", "S91", clinicos.InsuficienciaRenalCronica); writeBool(excelWorksheet, "R92", "S92", clinicos.InsuficienciaHepaticaCronica); writeBool(excelWorksheet, "R93", "S93", clinicos.Tabaquismo); excelWorksheet.Range["O94"].Value = clinicos.OtroCoMorbilidad; //DATOS DE LA UNIDAD MÉDICA excelWorksheet.Range["E104"].Value = unidadMedica.ServicioIngreso; if (unidadMedica.TipPaciente.Equals("A")) { excelWorksheet.Range["O104"].Value = "X"; } else if (unidadMedica.TipPaciente.Equals("H")) { excelWorksheet.Range["R104"].Value = "X"; } excelWorksheet.Range["F106"].Value = unidadMedica.FechaIngresoUnidad; writeBool(excelWorksheet, "P106", "R106", unidadMedica.CasoIngresadoUCI); writeBool(excelWorksheet, "P108", "R108", unidadMedica.CasoIntubado); writeBool(excelWorksheet, "R110", "S110", unidadMedica.CasoDiagnostNeumoniaClinica); writeBool(excelWorksheet, "R112", "S112", unidadMedica.CasoDiagnostNeumoniaRadiológica); //TRATAMIENTO if (!tratamiento.RecibioTrataAntipireticoAnalgesico.Equals(null)) { if (tratamiento.RecibioTrataAntipireticoAnalgesico.Equals("S")) { excelWorksheet.Range["K116"].Value = "X"; } else if (tratamiento.RecibioTrataAntipireticoAnalgesico.Equals("N")) { excelWorksheet.Range["L116"].Value = "X"; } else { excelWorksheet.Range["M116"].Value = "X"; } } excelWorksheet.Range["P118"].Value = tratamiento.FechaInicioTrataAntipireticoAnalgesico; excelWorksheet.Range["F121"].Value = tratamiento.Paracetamol ? "X" : ""; excelWorksheet.Range["F122"].Value = tratamiento.Ibuprofeno ? "X" : ""; excelWorksheet.Range["F123"].Value = tratamiento.AcidoAcetilsalicilico ? "X" : ""; excelWorksheet.Range["F124"].Value = tratamiento.ClonixinatoLisina ? "X" : ""; excelWorksheet.Range["K121"].Value = tratamiento.Naproxeno ? "X" : ""; excelWorksheet.Range["K122"].Value = tratamiento.Proxicam ? "X" : ""; excelWorksheet.Range["K123"].Value = tratamiento.MetamizolSodico ? "X" : ""; excelWorksheet.Range["Q121"].Value = tratamiento.Diclofenaco ? "X" : ""; excelWorksheet.Range["Q122"].Value = tratamiento.Ketorolaco ? "X" : ""; excelWorksheet.Range["Q123"].Value = tratamiento.OtroAntipireticoAnalgesico.Value ? "X" : ""; if (!tratamiento.RecibioTrataAntiviral.Equals(null)) { if (tratamiento.RecibioTrataAntiviral.Equals("S")) { excelWorksheet.Range["K126"].Value = "X"; } else if (tratamiento.RecibioTrataAntiviral.Equals("N")) { excelWorksheet.Range["L126"].Value = "X"; } else { excelWorksheet.Range["M126"].Value = "X"; } } excelWorksheet.Range["P128"].Value = tratamiento.FechaInicioTrataAntiviral; excelWorksheet.Range["F131"].Value = tratamiento.Amantadina ? "X" : ""; excelWorksheet.Range["F132"].Value = tratamiento.Rimantadina ? "X" : ""; excelWorksheet.Range["F133"].Value = tratamiento.Oseltamivir ? "X" : ""; excelWorksheet.Range["F133"].Value = tratamiento.Zanamivir ? "X" : ""; excelWorksheet.Range["J132"].Value = tratamiento.Peramivir ? "X" : ""; excelWorksheet.Range["O131"].Value = tratamiento.Ribavirina ? "X" : ""; excelWorksheet.Range["O132"].Value = tratamiento.OtroAntiviral; if (!tratamiento.RecibioTrataAntibiotico.Equals(null)) { if (tratamiento.RecibioTrataAntibiotico.Equals("S")) { excelWorksheet.Range["K135"].Value = "X"; } else if (tratamiento.RecibioTrataAntibiotico.Equals("N")) { excelWorksheet.Range["L135"].Value = "X"; } else { excelWorksheet.Range["M135"].Value = "X"; } } excelWorksheet.Range["P137"].Value = tratamiento.FechaInicioTrataAntibiotico; excelWorksheet.Range["F140"].Value = tratamiento.Penicilina ? "X" : ""; excelWorksheet.Range["F141"].Value = tratamiento.Dicloxacilina ? "X" : ""; excelWorksheet.Range["F142"].Value = tratamiento.Ampicilina ? "X" : ""; excelWorksheet.Range["F143"].Value = tratamiento.Amikacina ? "X" : ""; excelWorksheet.Range["F144"].Value = tratamiento.Doxiciclina ? "X" : ""; excelWorksheet.Range["F145"].Value = tratamiento.Cefalosporina ? "X" : ""; excelWorksheet.Range["J140"].Value = tratamiento.Clindamicina ? "X" : ""; excelWorksheet.Range["J141"].Value = tratamiento.Claritromicina ? "X" : ""; excelWorksheet.Range["J142"].Value = tratamiento.Ciprofloxacino ? "X" : ""; excelWorksheet.Range["J143"].Value = tratamiento.Meropenem ? "X" : ""; excelWorksheet.Range["J144"].Value = tratamiento.Vancomicina ? "X" : ""; excelWorksheet.Range["Q140"].Value = tratamiento.AmoxicilinaAcClauvulanico ? "X" : ""; excelWorksheet.Range["Q141"].Value = tratamiento.OtrosAntibioticos.Value ? "X" : ""; //LABORATORIO writeBool(excelWorksheet, "E149", "F149", laboratorio.SeTomoMuestraPaciente); excelWorksheet.Range["M149"].Value = laboratorio.LaboratorioDiagnostico; excelWorksheet.Range["G154"].Value = laboratorio.ExudadoFaringeo.Value ? "X" : ""; excelWorksheet.Range["G155"].Value = laboratorio.ExudadoNasofaringeo.Value ? "X" : ""; excelWorksheet.Range["N154"].Value = laboratorio.LavadoBronquial.Value ? "X" : ""; excelWorksheet.Range["N155"].Value = laboratorio.BiopsiaPulmon.Value ? "X" : ""; excelWorksheet.Range["E157"].Value = laboratorio.FechaTomaMuestra; excelWorksheet.Range["O157"].Value = laboratorio.FechaEnvioMuestra; excelWorksheet.Range["E159"].Value = laboratorio.FechaRecepcionMuestra; excelWorksheet.Range["L159"].Value = laboratorio.FechaResultado; excelWorksheet.Range["R159"].Value = laboratorio.Resultado; //EVOLUCIÓN if (!evolucion.Evolucion1.Equals(0)) { excelWorksheet.Range["D163"].Value = evolucion.Evolucion1; } excelWorksheet.Range["E165"].Value = evolucion.FechaEgreso; excelWorksheet.Range["E167"].Value = evolucion.FechaDefunción; excelWorksheet.Range["H169"].Value = evolucion.FolioCertificadoDefunción; writeBool(excelWorksheet, "O169", "P169", evolucion.Defuncion2019nCoV); //CONTACTOS int numCeldaContacto = 175; foreach (Contactos contacto in contactos) { excelWorksheet.Range["B" + numCeldaContacto].Value = contacto.NombreCompleto; if (contacto.Sexo.Equals("F")) { excelWorksheet.Range["F" + numCeldaContacto].Value = contacto.Sexo; } else if (contacto.Sexo.Equals("M")) { excelWorksheet.Range["G" + numCeldaContacto].Value = contacto.Sexo; } excelWorksheet.Range["H" + numCeldaContacto].Value = contacto.Edad; excelWorksheet.Range["I" + numCeldaContacto].Value = contacto.TipoContactoIDoED; excelWorksheet.Range["K" + numCeldaContacto].Value = contacto.CorreoElectronico; excelWorksheet.Range["M" + numCeldaContacto].Value = contacto.SignosSintomas; excelWorksheet.Range["R" + numCeldaContacto].Value = contacto.Observaciones; numCeldaContacto++; } excelWorksheet.PrintOutEx(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // excelWorkbook.SaveAs(@"C:\Temp\ecov1.xlsx"); excelWorkbook.SaveAs(destinoFile); excelApp.Quit(); DatosGenerales oTabla = null; using (EstudioEpidemiologicoEntities tablas = new EstudioEpidemiologicoEntities()) { oTabla = tablas.DatosGenerales.Find(dat.IdPersona); oTabla.FechaImpresion = DateTime.Now; tablas.Entry(oTabla).State = System.Data.Entity.EntityState.Modified; tablas.SaveChanges(); cargaDatos(); } } } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error en llenado del formato", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Cursor.Current = Cursors.Default; } void writeBool(Excel.Worksheet Hoja, string CeldaSi, string CeldaNo, bool?Valor) { if (Valor.Value) { Hoja.Range[CeldaSi].Value = "X"; } else { Hoja.Range[CeldaNo].Value = "X"; } } }
private void save_excel_Click(object sender, EventArgs e) { excelWorkbook.Close(null, null, null); excelApp.Quit(); Marshal.ReleaseComObject(excelApp); excelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); sv_name = save_name.Text; if (checkBox_multiple_run.Checked) { excelApp1 = new xc.Application(); excelApp1.Visible = true; excelWorkbook1 = excelApp1.Workbooks.Open(Open_for_save.FileName); excelSheets1 = excelWorkbook1.Worksheets; excelWorkSheet1 = excelSheets1.get_Item("Sheet1"); excelCell1 = excelWorkSheet1.UsedRange; for (int cc = 1; cc <= 16383; cc++) { ref_save_column = Convert.ToString(excelCell1.Cells[2, cc].Value2); if (ref_save_column == null) { start_column_save = cc; excelWorkSheet1.Cells[1, cc] = sv_name; excelWorkSheet1.Cells[2, cc] = "Frame No."; excelWorkSheet1.Cells[2, cc + 1] = "Flash period + Dark period"; excelWorkSheet1.Cells[2, cc + 2] = "Flash period"; break; } } for (int bb = 1; bb <= box.GetUpperBound(0); bb++) { excelWorkSheet1.Cells[bb + 2, start_column_save] = (bb - 1) + 1; excelWorkSheet1.Cells[bb + 2, start_column_save + 1] = box[bb - 1, 0]; excelWorkSheet1.Cells[bb + 2, start_column_save + 2] = box[bb - 1, 1]; } excelWorkbook1.Save(); excelWorkbook1.Close(null, null, null); excelApp1.Quit(); Marshal.ReleaseComObject(excelApp1); excelApp1 = null; GC.Collect(); GC.WaitForPendingFinalizers(); Array.Clear(box, 1, 51); Array.Clear(box, 2, 2); foreach (var series in show_freq_chart.Series) { series.Points.Clear(); } Application.Idle += multiple_file_run; Application.Idle -= save_excel_Click; } else { if (Open_for_save.ShowDialog() == System.Windows.Forms.DialogResult.OK) { excelApp1 = new xc.Application(); excelApp1.Visible = true; excelWorkbook1 = excelApp1.Workbooks.Open(Open_for_save.FileName); excelSheets1 = excelWorkbook1.Worksheets; excelWorkSheet1 = excelSheets1.get_Item("Sheet1"); excelCell1 = excelWorkSheet1.UsedRange; for (int cc = 1; cc <= 16383; cc++) { ref_save_column = Convert.ToString(excelCell1.Cells[2, cc].Value2); if (ref_save_column == null) { start_column_save = cc; excelWorkSheet1.Cells[1, cc] = sv_name; excelWorkSheet1.Cells[2, cc] = "Frame No."; excelWorkSheet1.Cells[2, cc + 1] = "Flash period + Dark period"; excelWorkSheet1.Cells[2, cc + 2] = "Flash period"; break; } } for (int bb = 1; bb <= box.GetUpperBound(0); bb++) { excelWorkSheet1.Cells[bb + 2, start_column_save] = (bb - 1) + 1; excelWorkSheet1.Cells[bb + 2, start_column_save + 1] = box[bb - 1, 0]; excelWorkSheet1.Cells[bb + 2, start_column_save + 2] = box[bb - 1, 1]; } excelWorkbook1.Save(); excelWorkbook1.Close(null, null, null); excelApp1.Quit(); Marshal.ReleaseComObject(excelApp1); excelApp1 = null; GC.Collect(); GC.WaitForPendingFinalizers(); Array.Clear(box, 1, 51); Array.Clear(box, 2, 2); foreach (var series in show_freq_chart.Series) { series.Points.Clear(); } } Application.Idle -= save_excel_Click; } }
private void otkritie() { try { string s = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile); string we = System.IO.Path.Combine(s, @"ProgramData\FreeRooms"); System.IO.Directory.CreateDirectory(we); excelapp = new Excel.Application(); excelappworkbooks = excelapp.Workbooks; excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, numbernedeli), 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); excelsheets = excelappworkbook.Worksheets; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelapp.Visible = true; } catch { MessageBox.Show("Файл не был найден, обновите базы данных"); } }
static void Main(string[] args) { DirectoryInfo excelDirectory = new DirectoryInfo(@"C:\Users\migue\OneDrive\Documentos\Visual Studio 2015\Projects\ConsolidarPlanilhaShippingPlan\ConsolidarPlanilhaShippingPlan\Planilhas"); FileInfo[] excelFiles = excelDirectory.GetFiles("0*"); foreach (var item in excelFiles) { Console.WriteLine(item.ToString()); } string workbookPathRede = excelDirectory + @"\" + excelFiles[4].ToString(); Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; string workbookPath = workbookPathRede; var workbooks = excelApp.Workbooks; Excel.Workbook excelWorkbook = workbooks.Open(workbookPath, false, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); excelWorkbook.Application.DisplayAlerts = false; Excel.Sheets excelSheets = excelWorkbook.Worksheets; Excel.Worksheet Worksheet = (Excel.Worksheet)excelSheets.get_Item(1); Excel.Workbook xlWorkBook; object misValue = System.Reflection.Missing.Value; xlWorkBook = excelApp.Workbooks.Add(misValue); Excel.Sheets excelSheetsDestino = xlWorkBook.Worksheets; Excel.Worksheet WorksheetConsolidado = (Excel.Worksheet)excelSheetsDestino.get_Item(1); int linhaPO = 1; int linhacabecalho; while (Worksheet.Cells[linhaPO, 1].Value != "PO") { linhaPO++; } linhacabecalho = linhaPO; linhaPO++; int countnull = linhaPO; while (Worksheet.Cells[countnull, 6].Value != null) { countnull++; } countnull--; DateTime data1 = new DateTime(2016, 08, 1); DateTime data2 = new DateTime(2016, 08, 19); int countETD = 0; if (Worksheet.Range["AH" + linhacabecalho].Value.ToString().IndexOf("ETD") != -1) { countETD++; } if (Worksheet.Range["AI" + linhacabecalho].Value.ToString().IndexOf("ETD") != -1) { countETD++; } if (Worksheet.Range["AJ" + linhacabecalho].Value.ToString().IndexOf("ETD") != -1) { countETD++; } Console.WriteLine(countETD); int countTerminoETD = -1; int linhaDestiny = 1; do { countTerminoETD++; int countTermino = linhaPO; while (countnull != countTermino) { string colunaETD = ""; try { if (countTerminoETD == 0) { colunaETD = "AG"; } else if (countTerminoETD == 1) { colunaETD = "AH"; } else if (countTerminoETD == 2) { colunaETD = "AI"; } else if (countTerminoETD == 3) { colunaETD = "AJ"; } string datas = Worksheet.Range[colunaETD + countTermino].Value.ToString(); datas = datas.Substring(0, 9); DateTime data = DateTime.Parse(datas); if (data >= data1 && data <= data2) { Console.WriteLine(data); 3 Excel.Range from = Worksheet.Range["A" + countTermino + ":AG" + countTermino]; Console.WriteLine(countTermino); Excel.Range to = WorksheetConsolidado.Range["A" + linhaDestiny + ":AG" + linhaDestiny]; from.Copy(Type.Missing); to.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); if (Worksheet.Range["AH" + linhacabecalho].Value.ToString().IndexOf("ETD") != -1) { Excel.Range from1 = Worksheet.Range["AH" + countTermino + ":AH" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AH" + linhaDestiny + ":AH" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } if (Worksheet.Range["AI" + linhacabecalho].Value.ToString().IndexOf("ETD") != -1) { Excel.Range from1 = Worksheet.Range["AI" + countTermino + ":AI" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AI" + linhaDestiny + ":AI" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } if (Worksheet.Range["AJ" + linhacabecalho].Value.ToString().IndexOf("ETD") != -1) { Excel.Range from1 = Worksheet.Range["AJ" + countTermino + ":AJ" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AJ" + linhaDestiny + ":AJ" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } if (countETD == 0) { Excel.Range from1 = Worksheet.Range["AH" + countTermino + ":AR" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AK" + linhaDestiny + ":AX" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } if (countETD == 1) { Excel.Range from1 = Worksheet.Range["AI" + countTermino + ":AT" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AK" + linhaDestiny + ":AX" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } if (countETD == 2) { Excel.Range from1 = Worksheet.Range["AJ" + countTermino + ":AV" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AK" + linhaDestiny + ":AX" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } if (countETD == 3) { Excel.Range from1 = Worksheet.Range["AK" + countTermino + ":AX" + countTermino]; Excel.Range to1 = WorksheetConsolidado.Range["AK" + linhaDestiny + ":AX" + linhaDestiny]; from1.Copy(Type.Missing); to1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } linhaDestiny++; } } catch (Exception err) { Console.WriteLine(err.Message); } countTermino++; } } while (countETD != countTerminoETD); /*Console.Read(); * Excel.Range from = Worksheet.Range["A"+linhaPO+ ":AG" + countnull]; * Excel.Range to = WorksheetConsolidado.Range["A1:AG" +countnull]; * from.Copy(Type.Missing); * to.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); */ Console.Read(); if (Worksheet.Cells[linhaPO, 33].Value.IndexOf("ETD") == -1) { Console.WriteLine("oi"); } else { Console.WriteLine("tchau"); } try { Worksheet.Range["AK:AK"].Insert(Excel.XlInsertShiftDirection.xlShiftToRight); } catch (Exception err) { Console.WriteLine(err.Message); } }
private string GetExcelSheetName(string pPath) { //打开一个Excel应用 _excelApp = new Excel.Application(); if (_excelApp == null) { throw new Exception("打开Excel应用时发生错误!"); } _books = _excelApp.Workbooks; //打开一个现有的工作薄 _book = _books.Add(pPath); _sheets = _book.Sheets; //选择第一个Sheet页 _sheet = (Excel._Worksheet)_sheets.get_Item(1); string sheetName = _sheet.Name; ReleaseCOM(_sheet); ReleaseCOM(_sheets); ReleaseCOM(_book); ReleaseCOM(_books); _excelApp.Quit(); ReleaseCOM(_excelApp); return sheetName; }
//---> 'masterSelect_Click' /* Controls the open file dialog for selecting the master record Excel file. Will perform error checking * after file is selected to ensure the master record file is formatted appropriately. Once successfully * loaded, error checking on the tab name will be performed. If the tab can't be found, an error message * stating the problem will be displayed to the message panel. */ private void masterSelect_Click(object sender, EventArgs e) { //check if workforce file has been loaded first if (workforceFile == null) { MessageBox.Show("Must select and load Workforce file before selecting Master Record file.", "Master Record File Error", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); return; } OpenFileDialog masterDialog = new OpenFileDialog(); //OpenFileDialog properties masterDialog.InitialDirectory = "c:\\"; masterDialog.Filter = "Excel Files (*.xls; *.xlsx)|*.xls; *.xlsx"; masterDialog.FilterIndex = 2; masterDialog.RestoreDirectory = true; //sentinel statement //will also perform error checking on file properties if (masterDialog.ShowDialog() == DialogResult.OK) { //display on message panel this.msgOut.Text += "M A S T E R F I L E\n"; //show file name in mainWindow this.masterName.Text = Path.GetFileName(masterDialog.FileName); masterFile = new Excel.Application(); //check if file is already open try { masterBook = masterFile.Workbooks.OpenXML(masterDialog.FileName, Type.Missing, Type.Missing); } catch (Exception error_3) { this.msgOut.Text += "File Is Already Open!\nError:\n" + error_3 + "\n\n"; this.killMaster(); return; } this.msgOut.Text += "\"Open File\" Check Passed!\n"; //display check passed message masterSheets = masterBook.Worksheets; //assign once checked for errors //check for proper tab formatting try { masterSheets.get_Item(this.masterTabName()); } catch (Exception error_4) { this.msgOut.Text += "Tab Name \"" + this.masterTabName() + "\" Not Found!\nError:\n" + error_4 + "\n\n"; this.killMaster(); return; } this.msgOut.Text += "\"Tab Name\" Check Passed!\n"; //display check passed message } this.msgOut.Text += "No Errors Found!\n\nReady to Merge Files\n"; this.programLocation.Text = "Ready to Merge Files"; this.progress.Text = String.Empty; return; }