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