private void flushEmptyMinute(DateTime oDateNow, int nMinute, int nLine)
{
string sPathFilename = Program.FOLDER_DESTINY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
string sPathFilenameCopy = Program.FOLDER_DESTINY_COPY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
int nRowStart = 4;
if (File.Exists(sPathFilename))
{
Microsoft.Office.Interop.Excel.Application oExcelApplication = new Microsoft.Office.Interop.Excel.Application();
oExcelApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks oExcelWorkBooks = oExcelApplication.Workbooks;
Microsoft.Office.Interop.Excel.Workbook oExcelWorkBook = oExcelWorkBooks.Open(sPathFilename);
for (int i = 1; i <= Program.nNumElements; i++)
{
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Volcando la fecha y la hora al Fichero Excel del contaminante " + i.ToString() + " la línea " + nLine, false);
}
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet = oExcelWorkBook.Worksheets[(i + 1)];
oWorkSheet.Cells[(nMinute + nRowStart + 1).ToString(), "A"] = "'" + oDateNow.Day.ToString().PadLeft(2, '0') + "/" + oDateNow.Month.ToString().PadLeft(2, '0') + "/" + oDateNow.Year + " " + oDateNow.Hour.ToString().PadLeft(2, '0') + ":" + nMinute.ToString().PadLeft(2, '0') + ":00";
nullExcelObject(oWorkSheet);
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fecha y hora volcandos al Fichero Excel del contaminante " + i.ToString() + " de la línea " + nLine + " de forma correcta", false);
}
}
try {
oExcelWorkBook.Save();
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos salvados al Fichero Excel de la línea " + nLine + " de forma correcta", false);
if (nMinute == 59)
{
if (File.Exists(sPathFilenameCopy))
{
File.Delete(sPathFilenameCopy);
}
oExcelWorkBook.SaveAs(sPathFilenameCopy, XlFileFormat.xlExcel5);
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Copia del Fichero Excel de la línea " + nLine + " realizada correctamente", false);
}
}
} catch (Exception) {
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{ERROR} Error salvando los datos del Fichero Excel de la línea " + nLine, false);
}
}
oExcelWorkBook.Close(false);
nullExcelObject(oExcelWorkBook);
oExcelWorkBooks.Close();
nullExcelObject(oExcelWorkBooks);
oExcelApplication.Application.Quit();
nullExcelObject(oExcelApplication);
killExcelProcesses();
FileFormatXEAC oFileFormatXEAC = new FileFormatXEAC();
if (oFileFormatXEAC.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC", false);
}
}
oFileFormatXEAC = null;
FileFormatXEACReports oFileFormatXEACReports = new FileFormatXEACReports();
if (oFileFormatXEACReports.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC - Reports", false);
}
}
oFileFormatXEACReports = null;
}
}
private static void ActionExcelWrite(string filename, Action <Workbook> action)
{
//引用Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
// if equal null means EXCEL is not installed.
Console.WriteLine("Excel is not properly installed!");
return;
}
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excel.Visible = false;
//打开时设置为全屏显式
//excel.DisplayFullScreen = true;
//初始化工作簿
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
//新增加一个 Excel 表(sheet)
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets[1];
try
{
action(workbook);
//是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
excel.DisplayAlerts = false;
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//workBook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
if (File.Exists(filename))
{
File.Delete(filename);
}
workbook.SaveAs(filename.Replace("/", "\\"));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
workbook.Close(false, Type.Missing, Type.Missing);
workbooks.Close();
//关闭退出
excel.Quit();
KillExcel.Kill(new IntPtr(excel.Hwnd));
//释放 COM 对象
//Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
//worksheet = null;
workbook = null;
workbooks = null;
excel = null;
GC.Collect();
}
}
private void timerHandler(object source, ElapsedEventArgs e)
{
DateTime oDateNow = DateTime.Now;
int nMinute = oDateNow.Minute;
lock (oThreadLockResource) {
Microsoft.Office.Interop.Excel.Application oExcelApplication = null;
Microsoft.Office.Interop.Excel.Workbook oExcelWorkBook = null;
HashSet <TagItem> oHashSetTagsValues = null;
try {
try {
if (nMinute == 0)
{
string sLogFile = Program.FOLDER_FILE_LOG + "EmissionsExcel_log_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + ".txt";
if (!File.Exists(sLogFile))
{
if (Program.bShowLog)
{
if (Program.oLogFile != null)
{
Program.oLogFile.Flush();
Program.oLogFile.Close();
}
Program.oLogFile = File.AppendText(sLogFile);
DateTime oDateTimeBefore = oDateNow.AddDays(-1);
string sLogFileBefore = Program.FOLDER_FILE_LOG + "EmissionsExcel_log_" + oDateTimeBefore.Year.ToString() + oDateTimeBefore.Month.ToString().PadLeft(2, '0') + oDateTimeBefore.Day.ToString().PadLeft(2, '0') + ".txt";
if (File.Exists(sLogFileBefore))
{
File.Move(sLogFileBefore, Program.FOLDER_FILE_LOG_HISTORY + "EmissionsExcel_log_" + oDateTimeBefore.Year.ToString() + oDateTimeBefore.Month.ToString().PadLeft(2, '0') + oDateTimeBefore.Day.ToString().PadLeft(2, '0') + ".txt");
}
}
}
}
} catch (Exception) { }
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{TIMERHANDLER_START:OK} Llamada al evento {timerHandler} al segundo " + oDateNow.Second, true);
}
if (nMinute != Program.nLastMinute)
{
Program.nLastMinute = nMinute;
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Creando valores de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
string sPathFilename = Program.FOLDER_DESTINY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
if ((!File.Exists(sPathFilename)) || ((File.Exists(sPathFilename)) && (nMinute == 0)))
{
if ((File.Exists(sPathFilename)) && (nMinute == 0))
{
File.Delete(sPathFilename);
}
oExcelApplication = new Microsoft.Office.Interop.Excel.Application();
oExcelApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks oExcelWorkBooks = oExcelApplication.Workbooks;
oExcelWorkBook = oExcelWorkBooks.Add();
createSheets(oExcelApplication, oDateNow);
removeDefaultSheets(oExcelApplication);
oExcelWorkBook.SaveAs(sPathFilename, XlFileFormat.xlExcel5);
oExcelWorkBook.Close(false);
nullExcelObject(oExcelWorkBook);
oExcelWorkBooks.Close();
nullExcelObject(oExcelWorkBooks);
oExcelApplication.Application.Quit();
nullExcelObject(oExcelApplication);
killExcelProcesses();
}
}
oHashSetTagsValues = TryExecuteSyncRead(Program.nTimeoutOPCRead);
if ((oHashSetTagsValues != null) && (oHashSetTagsValues.Count == (((Program.nNumElements * 9) + 2) * Program.nNumLines)))
{
DateTime oDateNowCurrent = DateTime.Now;
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Se han devuelto todos los valores del OPC de forma correcta sobre el minuto " + oDateNow.Minute + " a la hora: " + oDateNowCurrent.Hour + "h " + oDateNowCurrent.Minute + "m " + oDateNowCurrent.Second + "s", false);
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Creando valores de los contaminantes de la Línea " + nLine + " sobre el minuto " + oDateNow.Minute, false);
}
Array oArrayTagsValues = oHashSetTagsValues.ToArray <TagItem>();
string sPathFilename = Program.FOLDER_DESTINY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
string sPathFilenameCopy = Program.FOLDER_DESTINY_COPY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
if (File.Exists(sPathFilename))
{
oExcelApplication = new Microsoft.Office.Interop.Excel.Application();
oExcelApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks oExcelWorkBooks = oExcelApplication.Workbooks;
oExcelWorkBook = oExcelWorkBooks.Open(sPathFilename);
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel de la línea " + nLine + " abierto correctamente", false);
}
TagItem oTagItem1, oTagItem2, oTagItem3, oTagItem4, oTagItem5, oTagItem6, oTagItem7, oTagItem8, oTagItem9, oTagItem10, oTagItem11;
for (int i = 1; i <= Program.nNumElements; i++)
{
string sStatus = "V";
int nIndexBase = (((Program.nNumElements * 9) + 2) * (nLine - 1)) + ((i - 1) * 9) + 2;
oTagItem1 = (TagItem)oArrayTagsValues.GetValue((((Program.nNumElements * 9) + 2) * (nLine - 1)));
oTagItem2 = (TagItem)oArrayTagsValues.GetValue((((Program.nNumElements * 9) + 2) * (nLine - 1)) + 1);
oTagItem3 = (TagItem)oArrayTagsValues.GetValue(nIndexBase);
oTagItem4 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 1);
oTagItem5 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 2);
oTagItem6 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 3);
oTagItem7 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 4);
oTagItem8 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 5);
oTagItem9 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 6);
oTagItem10 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 7);
oTagItem11 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 8);
if (oTagItem1.getValue() == "1")
{
sStatus = "A";
}
else if (oTagItem2.getValue() == "1")
{
sStatus = "P";
}
else if (oTagItem5.getValue() == "1")
{
sStatus = "V";
}
else if (oTagItem6.getValue() == "1")
{
sStatus = "C";
}
else if (oTagItem7.getValue() == "1")
{
sStatus = "N";
}
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Volcando datos al Fichero Excel del contaminante " + i.ToString() + " de la línea " + nLine + " {" + oTagItem3.getName().ToString() + ":" + oTagItem3.getValue().ToString() + ", " + oTagItem4.getName().ToString() + ":" + oTagItem4.getValue().ToString() + ", Status: " + sStatus + "}", false);
}
ElementValue oElementValue = new ElementValue(oTagItem3.getValue(), oTagItem4.getValue(), sStatus, oTagItem8.getValue(), oTagItem9.getValue(), oTagItem10.getValue(), oTagItem11.getValue());
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet = oExcelWorkBook.Worksheets[(i + 1)];
flushMinuteValue(oWorkSheet, oDateNow, nMinute, oElementValue, (i - 1));
oElementValue = null;
oTagItem1 = null; oTagItem2 = null; oTagItem3 = null; oTagItem4 = null; oTagItem5 = null;
oTagItem6 = null; oTagItem7 = null; oTagItem8 = null; oTagItem9 = null; oTagItem10 = null;
oTagItem11 = null;
nullExcelObject(oWorkSheet);
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos volcados al Fichero Excel del contaminante " + i.ToString() + " de la línea " + nLine + " de forma correcta", false);
}
}
try {
oExcelWorkBook.Save();
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos salvados al Fichero Excel de la línea " + nLine + " de forma correcta", false);
if (nMinute == 59)
{
if (File.Exists(sPathFilenameCopy))
{
File.Delete(sPathFilenameCopy);
}
oExcelWorkBook.SaveAs(sPathFilenameCopy, XlFileFormat.xlExcel5);
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Copia del Fichero Excel de la línea " + nLine + " realizada correctamente", false);
}
}
} catch (Exception) {
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{ERROR} Error salvando los datos del Fichero Excel de la línea " + nLine, false);
}
}
oExcelWorkBook.Close(false);
nullExcelObject(oExcelWorkBook);
oExcelWorkBooks.Close();
nullExcelObject(oExcelWorkBooks);
oExcelApplication.Application.Quit();
nullExcelObject(oExcelApplication);
killExcelProcesses();
FileFormatXEAC oFileFormatXEAC = new FileFormatXEAC();
if (oFileFormatXEAC.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC", false);
}
}
oFileFormatXEAC = null;
FileFormatXEACReports oFileFormatXEACReports = new FileFormatXEACReports();
if (oFileFormatXEACReports.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC - Reports", false);
}
}
oFileFormatXEACReports = null;
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{ERROR} No existe el Fichero Excel de la Línea " + nLine, false);
}
}
oArrayTagsValues = null;
}
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} No se han devuelto todos los valores del OPC sobre el evento {timerHandler}", false);
}
killExcelProcesses();
if (Program.sMethod == "BEST_EFFORT")
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} Creando valores anteriores de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} Creando líneas vacias de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
if (Program.sMethod == "BEST_EFFORT")
{
cleanOPCReaderValues(oDateNow, nMinute, nLine);
}
else
{
flushEmptyMinute(oDateNow, nMinute, nLine);
}
}
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} Fin de la creación de valores anteriores de los contaminantes", false);
}
}
}
} catch (Exception err) {
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Se ha producido una excepción sobre el evento {timerHandler:" + err.ToString() + "}", false);
}
killExcelProcesses();
if (Program.sMethod == "BEST_EFFORT")
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Creando valores anteriores de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Creando líneas vacias de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
if (Program.sMethod == "BEST_EFFORT")
{
cleanOPCReaderValues(oDateNow, nMinute, nLine);
}
else
{
flushEmptyMinute(oDateNow, nMinute, nLine);
}
}
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Fin de la creación de valores anteriores de los contaminantes", false);
}
}
// Clean memory
oHashSetTagsValues = null;
GC.Collect();
GC.WaitForPendingFinalizers();
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{TIMERHANDLER_END:OK} Fin de la llamada al evento {timerHandler}", false);
}
}
}
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 InternalFormat Import(string FileName)
{
InternalFormat data = null;
// open excel document
Excel.Application App = new Excel.Application();
Excel.Workbooks Workbooks = App.Workbooks;
Excel.Workbook Workbook = Workbooks.Open(FileName);
Excel._Worksheet Worksheet = App.ActiveSheet;
Excel.Range Range = Worksheet.UsedRange;
// read document data
var Cells = Range.Value2;
int rowCount = Cells.GetLength(0);
int columnCount = Cells.GetLength(1);
data = new InternalFormat();
// read native and other cultures
data.Cultures = new List <string>();
for (int col = 3; col <= columnCount; col++)
{
if (Cells[1, col] != null)
{
// third column is NativeCulture
if (col == 3)
{
data.NativeCulture = Cells[1, col];
}
data.Cultures.Add(Cells[1, col]);
}
}
int index = 2;
int cultureCount = data.Cultures.Count;
List <InternalKey> keys = new List <InternalKey>(rowCount / 2);
// read all translation keys
for (; Cells[index, 1].ToString() != serviceData; index++)
{
InternalKey key = new InternalKey();
key.Key = InternalNamespace.SplitFullName(Cells[index, 2])[1];
key.Translations = new List <InternalText>(cultureCount);
for (int culture = 0; culture < cultureCount; culture++)
{
InternalText translation = new InternalText();
translation.Culture = data.Cultures[culture];
translation.Text = Cells[index, culture + 3];
if (translation.Text == null)
{
translation.Text = "";
}
else // replace \n to \r\n
{
translation.Text = Regex.Replace(translation.Text, "(?<!\r)\n", "\r\n");
}
key.Translations.Add(translation);
}
keys.Add(key);
}
int indexOfServiceData = index;
data.Subnamespaces = new List <InternalNamespace>(rowCount / 2);
InternalNamespace lastNS = null;
InternalRecord lastRec = null;
index++;
for (; index < rowCount + 1; index++)
{
string source = Cells[index, 1];
string ns = Cells[index, 2];
string key = Cells[index, 3];
string path = Cells[index, 4];
if (lastNS == null || lastNS.Name != ns)
{
lastNS = new InternalNamespace();
lastNS.Name = ns;
lastNS.Children = new List <InternalRecord>();
data.Subnamespaces.Add(lastNS);
lastRec = null;
}
if (lastRec == null || lastRec.Source != source)
{
lastRec = new InternalRecord();
lastRec.Source = source;
lastRec.Keys = new List <InternalKey>();
lastNS.Children.Add(lastRec);
}
InternalKey ikey = keys[index - indexOfServiceData - 1];
if (ikey.Key != key)
{
throw new FormatException("Unexpected key: " + key + "!");
}
ikey.Path = path;
ikey.parent = lastRec;
lastRec.Keys.Add(ikey);
}
// close excel and clear all headres
Marshal.ReleaseComObject(Range); Range = null;
Marshal.ReleaseComObject(Worksheet); Worksheet = null;
Workbook.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(Workbook); Workbook = null;
Workbooks.Close();
Marshal.ReleaseComObject(Workbooks); Workbooks = null;
App.Quit();
Marshal.ReleaseComObject(App); App = null;
return(data);
}
public void coutExcel(DataGridView dataGridView1)
{
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
//无数据则不再执行
if (dataGridView1.Rows.Count == 0)
{
return;
}
//实例化Excel.Application对象
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;//为TRUE在导出时显示EXCEL界面
if (excel == null)
{
MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
//int m = 0, n = 0;
//生成EXCEL列名
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名
}
//存储数据
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
System.Diagnostics.Process.Start(strName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
// Returns true if the creation succeed, if not returns false
public static bool CreateExcelFile(string name, string customUI = null)
{
// Validate overriding
if (File.Exists(name))
{
Console.Write("There is an excel file already created with this name and path. \nWould you like to override? (y/n) --> ");
ConsoleKeyInfo key;
int x = 0x0;
int pos = Console.CursorLeft;
bool canceled = true;
while (x != 0x1B)
{
key = Console.ReadKey();
x = key.KeyChar;
if (!(x == 0x59 || x == 0x79 || x == 0x4E || x == 0x6E))
{
if (x != 0x1B)
{
if (x != 0x8)
{
Console.Write("\b \b");
}
else
{
Console.Write(" ");
}
Console.CursorLeft = pos;
}
else
{
Console.Write("xn");
}
}
else
{
canceled = x == 0x4E || x == 0x6E;
break;
}
}
if (canceled)
{
Console.WriteLine("\nProccess Canceled");
return(false);
}
else
{
File.Delete(name);
Console.WriteLine("\nOverriding file...");
}
}
// Gets the current office version
Excel.Application xlApp;
if (version == null)
{
xlApp = new Excel.Application();
version = xlApp.Version;
xlApp.Quit();
while (Marshal.ReleaseComObject(xlApp) != 0)
{
}
}
DisableTrustCenterSecurity();
xlApp = new Excel.Application();
Excel.Workbooks xlWbks = xlApp.Workbooks;
Excel.Workbook xlWbk = xlWbks.Add();
Excel.XlFileFormat xlFileFormat = Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
// Creating file
AddModule(xlWbk, Executable.Files.VBE.Modules.Callbacks);
name = Regex.Replace(name, "[/]", "\\");
xlWbk.SaveAs(name, xlFileFormat);
xlWbk.Close(true);
while (Marshal.ReleaseComObject(xlWbk) != 0)
{
}
xlWbks.Close();
while (Marshal.ReleaseComObject(xlWbks) != 0)
{
}
xlApp.Quit();
while (Marshal.ReleaseComObject(xlApp) != 0)
{
}
Console.WriteLine(@$ "Excel file successfully created: '{name}'");
bool added = CustomUI.AddCustomUI(name, customUI);
EnableTrustCenterSecurity();
return(added);
}
/// <summary>
/// excel批量转为pdf
/// </summary>
/// <param name="fromExcellist"></param>
/// <param name="printDir"></param>
/// <returns></returns>
public string[] ExcellistToPDF(string fromExcellist, string printDir)
{
string[] strArrays = fromExcellist.Split(new char[] { ',' });//逗号分隔数组
List <string> aPdfFile = new List <string>();
object missing = Type.Missing;
EXCEL.ApplicationClass excel = null;
EXCEL.Workbooks workBooks = null;
EXCEL.Workbook wb = null;
try
{
excel = new EXCEL.ApplicationClass();
excel.DisplayAlerts = false;
workBooks = excel.Workbooks;
string sPdfFile = "";
if (printDir == "")
{
printDir = "C:\\Windows\\Temp\\";
}
for (int i = 0; i < strArrays.Length; i++)
{
wb = workBooks.Open(strArrays[i], missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
sPdfFile = printDir + System.Guid.NewGuid().ToString() + ".pdf";
wb.ExportAsFixedFormat(
EXCEL.XlFixedFormatType.xlTypePDF,
sPdfFile,
EXCEL.XlFixedFormatQuality.xlQualityStandard, //可设置为 xlQualityStandard 或 xlQualityMinimum。
true, //包含文档属性
false, //如果设置为 True，则忽略在发布时设置的任何打印区域。如果设置为 False，则使用在发布时设置的打印区域。
Type.Missing, //发布的起始页码。如果省略此参数，则从起始位置开始发布。
Type.Missing, //发布的终止页码。如果省略此参数，则发布至最后一页。
false, //是否发布文件后在查看器中显示文件。
Type.Missing);
wb.Save();
if (wb != null)
{
wb.Close(false, missing, missing);
Marshal.ReleaseComObject(wb);
wb = null;
}
aPdfFile.Add(sPdfFile);
}
return(aPdfFile.ToArray());
}
catch (Exception ex)
{
classLims_NPOI.WriteLog(ex, "");
return(null);
}
finally
{
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBooks = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public bool ExcelWorkbookPrintToPDF(string fromExcelPath, string toPath)
{
bool flag = false;
if (File.Exists(fromExcelPath))
{
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
object missing = Type.Missing;
try
{
if (fromExcelPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的源文件路径不能为空。");
}
if (toPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的目标文件路径不能为空。");
}
excel = new EXCEL.ApplicationClass();
workBooks = excel.Workbooks;
Type type = workBooks.GetType();
workBook = workBooks.Open(fromExcelPath, missing, true,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//先使用分页视图打开,EXCEl获取 HPageBreaks 需要在分页视图中
excel.ActiveWindow.View = EXCEL.XlWindowView.xlPageBreakPreview;
//int hpbCount = classExcelMthd.getSheetPageCount(workBook, 1);
//按照设置好的打印区域发布为pdf
workBook.PrintOutEx(missing, missing, missing, false, missing,
true, false, "ZZY", true);
//再还原为普通视图
excel.ActiveWindow.View = EXCEL.XlWindowView.xlNormalView;
flag = true;
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
flag = false;
}
finally
{
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
/// <summary>
/// 重载DataGridView输出另存为Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="strKeyWord"></param>
/// <param name="saveFileName"></param>
/// <returns></returns>
public bool Dgv2Excel(DataGridView dgv, string strKeyWord, string saveFileName)
{
//string fileName = "";
MsExcel.Application xlApp = new MsExcel.Application();
IntPtr t = new IntPtr(xlApp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
if (saveFileName.IndexOf(":") < 0)//被点了取消
{
return(false);
}
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel。", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
MsExcel.Workbooks workbooks = xlApp.Workbooks;
MsExcel.Workbook workbook = workbooks.Add(MsExcel.XlWBATemplate.xlWBATWorksheet);
MsExcel.Worksheet worksheet = (MsExcel.Worksheet)workbook.Worksheets[1];//取得sheet1
//当存储总分值表格时
if (strKeyWord.Equals("总分值"))
{
//写入标题
int clmnCounter = 0;
for (int i = 0, j = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].HeaderText.Contains("排序") ||
dgv.Columns[i].HeaderText.Contains("区块") ||
dgv.Columns[i].HeaderText.Contains("总分值"))
{
worksheet.Cells[1, j + 1] = dgv.Columns[i].HeaderText;
j++;
}
if (dgv.Columns[i].HeaderText.Contains("总分值"))
{
i++;
clmnCounter = i;
break;
}
}
//写入数值
for (int r = 0; r < dgv.Rows.Count; r++)
{
for (int i = 0, j = 0; i < clmnCounter; i++)
{
if (dgv.Columns[i].HeaderText.Contains("排序") ||
dgv.Columns[i].HeaderText.Contains("区块") ||
dgv.Columns[i].HeaderText.Contains("总分值"))
{
worksheet.Cells[r + 2, j + 1] = dgv.Rows[r].Cells[i].Value;
j++;
}
}
//System.Windows.Forms.Application.DoEvents(); //实时刷新窗体的显示界面
}
}
//当存储权重值表格时
if (strKeyWord.Equals("权重"))
{
//写入标题
//int clmnCounter = 0;
for (int i = 0, j = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].HeaderText.Contains("权重"))
{
worksheet.Cells[1, j + 1] = dgv.Columns[i].HeaderText;
j++;
}
}
//写入数值
//for (int r = 0; r < dgv.Rows.Count; r++)
//{
for (int i = 0, j = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].HeaderText.Contains("权重"))
{
worksheet.Cells[2, j + 1] = dgv.Rows[2].Cells[i].Value;
j++;
}
}
//System.Windows.Forms.Application.DoEvents(); //实时刷新窗体的显示界面
//}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
try
{
if (saveFileName != "")
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
return(true);
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return(false);
}
finally
{
workbook.Close();
workbooks.Close();
QuitExcelApp(xlApp, k);
}
}
/// <summary>
/// 打印workbook,使用另存为功能转pdf,指定打印页码
/// </summary>
/// <param name="fromExcelPath">源excel路径</param>
/// <param name="toPath">输出路径</param>
/// <param name="pageFrom">打印起始页码</param>
/// <param name="pageTo">打印结束页码</param>
/// <returns>成功或失败</returns>
public bool SaveExcelWorkbookAsPDFWithPage(string fromExcelPath, string toPath, int pageFrom, int pageTo)
{
bool flag = false;
if (File.Exists(fromExcelPath))
{
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
object missing = Type.Missing;
try
{
if (fromExcelPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的源文件路径不能为空。");
}
if (toPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的目标文件路径不能为空。");
}
excel = new EXCEL.ApplicationClass();
workBooks = excel.Workbooks;
Type type = workBooks.GetType();
workBook = workBooks.Open(fromExcelPath, missing, true,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//先使用分页视图打开,EXCEl获取 HPageBreaks 需要在分页视图中
excel.ActiveWindow.View = EXCEL.XlWindowView.xlPageBreakPreview;
//int hpbCount = classExcelMthd.getSheetPageCount(workBook, 1);
//按照设置好的打印区域发布为pdf
workBook.ExportAsFixedFormat(
EXCEL.XlFixedFormatType.xlTypePDF,
toPath,
EXCEL.XlFixedFormatQuality.xlQualityStandard, //可设置为 xlQualityStandard 或 xlQualityMinimum。
true, //包含文档属性
false, //如果设置为 True，则忽略在发布时设置的任何打印区域。如果设置为 False，则使用在发布时设置的打印区域。
pageFrom, //发布的起始页码。如果省略此参数，则从起始位置开始发布。
pageTo, //发布的终止页码。如果省略此参数，则发布至最后一页。
false, //是否发布文件后在查看器中显示文件。
Type.Missing);
//再还原为普通视图
excel.ActiveWindow.View = EXCEL.XlWindowView.xlNormalView;
flag = true;
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
flag = false;
}
finally
{
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
/// <summary>
/// 将数据集存储为Excel文件。
/// </summary>
/// <param name="p_ds">要导出的数据集。</param>
private static void SaveAsExcelSetFile(object p_ds)
{
System.Data.DataSet ds = (System.Data.DataSet)p_ds;
if(ds == null || ds.Tables.Count == 0)
{
MessageUtil.ShowError("没有可保存的数据");
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择文件存放路径";
sfd.FileName = "导出数据";
sfd.Filter = "Excel文档(*.xls)|*.xls";
if(sfd.ShowDialog() != DialogResult.OK)
{
return;
}
string FileName = sfd.FileName.ToLower();
if(!FileName.Contains(".xls"))
{
FileName += ".xls";
}
if(FileName.Substring(FileName.LastIndexOf(Path.DirectorySeparatorChar)).Length <= 5)
{
MessageUtil.ShowThreadMessage("文件保存失败，文件名不能为空！");
return;
}
if(sfd.FileName != "")
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if(excelApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
else
{
MessageUtil.ShowThreadMessage("正在导出数据，请稍候...");
DateTime start = DateTime.Now;
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
object objMissing = System.Reflection.Missing.Value;
for(int m = 0; m < ds.Tables.Count; m++)
{
System.Data.DataTable dt = ds.Tables[m];
worksheet = (Worksheet)workbook.ActiveSheet;
worksheet.Name = dt.TableName;
for(int col = 1; col <= dt.Columns.Count; col++)
{
worksheet.Cells[1, col] = dt.Columns[col - 1].Caption.ToString();
}
for(int i = 1; i <= dt.Rows.Count; i++)
{
for(int j = 1; j <= dt.Columns.Count; j++)
{
worksheet.Cells[i + 1, j] = dt.Rows[i - 1][j - 1].ToString();
}
}
if(m < ds.Tables.Count - 1)
{
workbook.Sheets.Add(objMissing, objMissing, 1, XlSheetType.xlWorksheet);
}
}
workbook.Saved = true;
workbook.SaveCopyAs(sfd.FileName);
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
GC.Collect();
DateTime end = DateTime.Now;
int iTimeSapn = (end.Minute - start.Minute) * 60 + (end.Second - start.Second);
MessageUtil.ShowMessage("数据导出完毕,用时" + (iTimeSapn / 60).ToString() + "分" + (iTimeSapn % 60).ToString() + "秒");
}
}
}
/// <summary>
/// 将数据存储为Excel文件。
/// </summary>
/// <param name="p_dt">要保存的数据表。</param>
private static void SaveAsExcelTableFile(object p_dt)
{
System.Data.DataTable dt = (System.Data.DataTable)p_dt;
if(dt.Rows.Count == 0)
{
MessageUtil.ShowError("没有可保存的数据");
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择文件存放路径";
sfd.FileName = "导出数据";
sfd.Filter = "Excel文档(*.xls)|*.xls";
if(sfd.ShowDialog() != DialogResult.OK)
{
return;
}
string FileName = sfd.FileName.ToLower();
if(!FileName.Contains(".xls"))
{
FileName += ".xls";
}
if(FileName.Substring(FileName.LastIndexOf(Path.DirectorySeparatorChar)).Length <= 5)
{
MessageUtil.ShowThreadMessage("文件保存失败，文件名不能为空！");
return;
}
if(sfd.FileName != "")
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if(excelApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
else
{
MessageUtil.ShowThreadMessage("正在导出数据，请稍候...");
DateTime start = DateTime.Now;
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
for(int col = 1; col <= dt.Columns.Count; col++)
{
worksheet.Cells[1, col] = dt.Columns[col - 1].Caption.ToString();
}
for(int i = 0; i < dt.Rows.Count; i++)
{
for(int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
workbook.Saved = true;
workbook.SaveCopyAs(sfd.FileName);
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
//使用垃圾回收可以关闭EXCEL.EXE进程
GC.Collect();
DateTime end = DateTime.Now;
int iTimeSpan = (end.Minute - start.Minute) * 60 + (end.Second - start.Second);
MessageUtil.ShowMessage("数据导出完毕,用时" + iTimeSpan.ToString() + "秒");
}
}
}
private bool CreateXLS(DataTable AppDT, DataTable DetailDT, string title)
{
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();
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++;
}
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++;
}
}
}
//保存
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);
}
private void cleanOPCReaderValues(DateTime oDateNow, int nMinute, int nLine)
{
string sPathFilename = Program.FOLDER_DESTINY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
string sPathFilenameCopy = Program.FOLDER_DESTINY_COPY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
if (File.Exists(sPathFilename))
{
Microsoft.Office.Interop.Excel.Application oExcelApplication = new Microsoft.Office.Interop.Excel.Application();
oExcelApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks oExcelWorkBooks = oExcelApplication.Workbooks;
Microsoft.Office.Interop.Excel.Workbook oExcelWorkBook = oExcelWorkBooks.Open(sPathFilename);
for (int i = 1; i <= Program.nNumElements; i++)
{
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Volcando datos al Fichero Excel del contaminante " + i.ToString() + " la línea " + nLine + " {" + Program.oBeforeElementsValues[i - 1].getDirtyValue().ToString() + ", " + Program.oBeforeElementsValues[i - 1].getRealValue().ToString() + ", " + Program.oBeforeElementsValues[i - 1].getType() + "}", false);
}
ElementValue oElementValue = new ElementValue(Program.oBeforeElementsValues[i - 1].getDirtyValue(), Program.oBeforeElementsValues[i - 1].getRealValue(), Program.oBeforeElementsValues[i - 1].getType(), Program.oBeforeElementsValues[i - 1].getQAL2Min(), Program.oBeforeElementsValues[i - 1].getQAL2Max(), Program.oBeforeElementsValues[i - 1].getQAL2A(), Program.oBeforeElementsValues[i - 1].getQAL2B());
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet = oExcelWorkBook.Worksheets[(i + 1)];
flushMinuteValue(oWorkSheet, oDateNow, nMinute, oElementValue, (i - 1));
oElementValue = null;
nullExcelObject(oWorkSheet);
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos volcados al Fichero Excel del contaminante " + i.ToString() + " de la línea " + nLine + " de forma correcta", false);
}
}
try {
oExcelWorkBook.Save();
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos salvados al Fichero Excel de la línea " + nLine + " de forma correcta", false);
if (nMinute == 59)
{
if (File.Exists(sPathFilenameCopy))
{
File.Delete(sPathFilenameCopy);
}
oExcelWorkBook.SaveAs(sPathFilenameCopy, XlFileFormat.xlExcel5);
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Copia del Fichero Excel de la línea " + nLine + " realizada correctamente", false);
}
}
} catch (Exception) {
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{ERROR} Error salvando los datos del Fichero Excel de la línea " + nLine, false);
}
}
oExcelWorkBook.Close(false);
nullExcelObject(oExcelWorkBook);
oExcelWorkBooks.Close();
nullExcelObject(oExcelWorkBooks);
oExcelApplication.Application.Quit();
nullExcelObject(oExcelApplication);
killExcelProcesses();
FileFormatXEAC oFileFormatXEAC = new FileFormatXEAC();
if (oFileFormatXEAC.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC", false);
}
}
oFileFormatXEAC = null;
FileFormatXEACReports oFileFormatXEACReports = new FileFormatXEACReports();
if (oFileFormatXEACReports.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC - Reports", false);
}
}
oFileFormatXEACReports = null;
}
}
/// <summary>
/// EXCEL转PDF
/// </summary>
/// <param name="strSourceFile">要转换的EXCEL文件路径</param>
/// <param name="strTargetFile">目标文件</param>
/// <returns>转换结果：TRUE FALSE</returns>
public bool ExcelConvertTOPDF(string strSourceFile, string strTargetFile)
{
bool flag = false;
if (File.Exists(strSourceFile))
{
//转换成的上档格式PDF
EXCEL.XlFixedFormatType targetType = EXCEL.XlFixedFormatType.xlTypePDF;
object targetFile = strTargetFile;
object missing = Type.Missing;
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
EXCEL.Worksheet sheet = null;
try
{
excel = new EXCEL.ApplicationClass();
//excel.DisplayAlerts = true;
workBooks = excel.Workbooks;
workBook = workBooks.Open(strSourceFile, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//设置格式，导出成PDF
sheet = (EXCEL.Worksheet)workBook.Worksheets[1];//下载从1开始
//把sheet设置成横向
//sheet.PageSetup.Orientation = EXCEL.XlPageOrientation.xlLandscape;
//可以设置sheet页的其他相关设置，不列举
sheet.ExportAsFixedFormat(targetType, targetFile, EXCEL.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
flag = true;
}
catch (Exception ex)
{
classLims_NPOI.WriteLog(ex, "");
flag = false;
}
finally
{
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
//Marshal.FinalReleaseComObject(sheet);
sheet = null;
}
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
//flag = KillSpecialExcel(excel);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
public void ExportToExcelFun(System.Data.DataTable dt)
{
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xls";
saveDia.Title = "导出为Excel文件";
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK &&
!string.Empty.Equals(saveDia.FileName))
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9; //字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit(); //自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return;
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion
MessageBox.Show("导出成功!");
}
}
//private void logFichasAbertasExcel(DateTime pDate, string pTime, int pFichas)
//{
// Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// Excel.Workbooks oWBs = oXL.Workbooks;
// Excel._Workbook oWB;
// Excel._Worksheet oSheet;
// string a = "1";
// if (File.Exists(@"LogFichasAbertas.xlsx"))
// oWB = oWBs.Open(@"LogFichasAbertas.xlsx");
// else
// oWB = oWBs.Add(Type.Missing);
// a = "2";
// oSheet = oWB.ActiveSheet;
// a = "3";
// Excel.Range r = oSheet.Range["A:A"];
// Excel.Range found = null;
// Excel.Range endRange = null;
// Excel.Range rowRange = null;
// Excel.Range cells = oSheet.Cells;
// Excel.Range cellDate = null;
// Excel.Range cellFicha = null;
// Excel.Font font = null;
// a = "4";
// try
// {
// int col = 2;
// a = "5";
// found = r.Find(pDate.ToString("yyMMdd"), SearchOrder: Excel.XlSearchOrder.xlByRows, LookIn: Excel.XlFindLookIn.xlValues);
// if (found == null)
// {
// a = "6";
// endRange = r.End[Excel.XlDirection.xlToRight];
// col = endRange.Column > 16000 ? 2 : endRange.Column + 1;
// a = "7";
// cellDate = cells[1, col];
// cellDate.Value = pDate.ToString("yyMMdd");
// font = cellDate.Font;
// font.Bold = true;
// cellDate.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// a = "8";
// }
// else
// col = found.Column;
// a = "9";
// rowRange = r.Find(pTime, SearchOrder: Excel.XlSearchOrder.xlByColumns);
// int row = 14;// rowRange.Row;
// a = "10";
// cellFicha = cells[row, col];
// cellFicha.Value = pFichas;
// a = "11";
// oXL.DisplayAlerts = false;
// oWB.SaveAs(@"LogFichasAbertas.xlsx", ConflictResolution: Excel.XlSaveConflictResolution.xlLocalSessionChanges);
// eventLog1.WriteEntry("SUCCESS! logFichasAbertasExcel - " + a, EventLogEntryType.Error, 10);
// }
// catch (Exception ex)
// {
// eventLog1.WriteEntry("ERROR: logFichasAbertasExcel - " + a + " -- " +ex.Message, EventLogEntryType.Error, 10);
// }
// finally
// {
// oWB.Close();
// oWBs.Close();
// oXL.Quit();
// releaseObject(font);
// releaseObject(found);
// releaseObject(r);
// releaseObject(cells);
// releaseObject(cellFicha);
// releaseObject(cellDate);
// releaseObject(endRange);
// releaseObject(rowRange);
// releaseObject(oSheet);
// releaseObject(oWB);
// releaseObject(oWBs);
// releaseObject(oXL);
// }
//}
//private void releaseObject(object obj)
//{
// if (obj == null) return;
// try
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
// obj = null;
// }
// catch (Exception ex)
// {
// obj = null;
// eventLog1.WriteEntry("ERROR: releaseObject - " + ex.Message, EventLogEntryType.Error, 10);
// }
// finally
// {
// GC.Collect();
// }
//}
private void logFichasAbertasExcel(DateTime pDate, string pTime, int pFichas)
{
Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
if (File.Exists(@"D:\Avenca\LogFichasAbertas.xlsx"))
{
oWB = oWBs.Open(@"D:\Avenca\LogFichasAbertas.xlsx");
}
else
{
oWB = oWBs.Add(Type.Missing);
}
oSheet = oWB.ActiveSheet;
Excel.Range r = oSheet.Range["A1"];
Excel.Range found = null;
Excel.Range endRange = null;
Excel.Range rowRange = null;
Excel.Range cells = oSheet.Cells;
Excel.Range cellDate = null;
Excel.Range cellFicha = null;
Excel.Font font = null;
try
{
int col = 2;
found = r.Find(pDate.ToString("yyMMdd"), SearchOrder: Excel.XlSearchOrder.xlByRows, LookIn: Excel.XlFindLookIn.xlValues);
if (found == null)
{
endRange = r.End[Excel.XlDirection.xlToRight];
col = endRange.Column > 16000 ? 2 : endRange.Column + 1;
cellDate = cells[1, col];
cellDate.Value = pDate.ToString("yyMMdd");
font = cellDate.Font;
font.Bold = true;
cellDate.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
else
{
col = found.Column;
}
rowRange = r.Find(pTime, SearchOrder: Excel.XlSearchOrder.xlByColumns);
int row = rowRange.Row;
cellFicha = cells[row, col];
cellFicha.Value = pFichas;
oXL.DisplayAlerts = false;
oWB.SaveAs(@"D:\Avenca\LogFichasAbertas.xlsx", ConflictResolution: Excel.XlSaveConflictResolution.xlLocalSessionChanges);
}
catch (Exception ex)
{
eventLog1.WriteEntry("ERROR: logFichasAbertasExcel - " + ex.Message, EventLogEntryType.Error, 10);
}
finally
{
oWB.Close();
oWBs.Close();
oXL.Quit();
releaseObject(font);
releaseObject(found);
releaseObject(r);
releaseObject(cells);
releaseObject(cellFicha);
releaseObject(cellDate);
releaseObject(endRange);
releaseObject(rowRange);
releaseObject(oSheet);
releaseObject(oWB);
releaseObject(oWBs);
releaseObject(oXL);
}
}
public string convertFiles(IList <string> filePaths)
{
m_filePaths = filePaths;
m_fileCount = m_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;
int totalCountQas = 0;
int totalCountParagraphs = 0;
int sheet1TotalRowCount = 0;
ArrayList splitedFileName = new ArrayList();
foreach (var item in m_filePaths)
{
string[] temp;
m_path = item;
var missing = Type.Missing;
temp = m_path.Split('_');
splitedFileName.Add(temp);
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 < sheet1RowCount; row++)
{
sheet1ValueArray[row, 0] = row + 1;
sheet1ValueArray[row, 1] = topTag.version;
sheet1ValueArray[row, 2] = topTag.creator;
sheet1ValueArray[row, 3] = topTag.progress;
sheet1ValueArray[row, 4] = topTag.formatt;
sheet1ValueArray[row, 5] = topTag.time;
sheet1ValueArray[row, 6] = topTag.check;
sheet1ValueArray[row, 7] = topTag.firstfile;
sheet1ValueArray[row, 8] = topTag.secondfile;
}
// * name2 & name3 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
sheet1ValueArray[currentRow, 9] = data[d].title;
sheet1ValueArray[currentRow, 10] = paragraphs[d][p].context;
sheet1ValueArray[currentRow, 11] = paragraphs[d][p].context_en;
sheet1ValueArray[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, 13] = qas[d][p][q].id;
sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQt1;
sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseQf1;
sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseSat1;
sheet1ValueArray[currentRow, 17] = qas[d][p][q].confuseLat1;
sheet1ValueArray[currentRow, 18] = qas[d][p][q].question;
sheet1ValueArray[currentRow, 19] = qas[d][p][q].question_en;
sheet1ValueArray[currentRow, 20] = qas[d][p][q].question_tagged1;
sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionType1;
sheet1ValueArray[currentRow, 22] = qas[d][p][q].questionFocus1;
sheet1ValueArray[currentRow, 23] = qas[d][p][q].questionSAT1;
sheet1ValueArray[currentRow, 24] = qas[d][p][q].questionLAT1;
sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseQt2;
sheet1ValueArray[currentRow, 26] = qas[d][p][q].confuseQf2;
sheet1ValueArray[currentRow, 27] = qas[d][p][q].confuseSat2;
sheet1ValueArray[currentRow, 28] = qas[d][p][q].confuseLat2;
sheet1ValueArray[currentRow, 29] = qas[d][p][q].question_tagged2; //
sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionType2; //
sheet1ValueArray[currentRow, 31] = qas[d][p][q].questionFocus2; //
sheet1ValueArray[currentRow, 32] = qas[d][p][q].questionSAT2; //
sheet1ValueArray[currentRow, 33] = qas[d][p][q].questionLAT2;
sheet1ValueArray[currentRow, 34] = qas[d][p][q].confuseQt3;
sheet1ValueArray[currentRow, 35] = qas[d][p][q].confuseQf3;
sheet1ValueArray[currentRow, 36] = qas[d][p][q].confuseSat3;
sheet1ValueArray[currentRow, 37] = qas[d][p][q].confuseLat3;
sheet1ValueArray[currentRow, 38] = qas[d][p][q].question_tagged3;
sheet1ValueArray[currentRow, 39] = qas[d][p][q].questionType3;
sheet1ValueArray[currentRow, 40] = qas[d][p][q].questionFocus3;
sheet1ValueArray[currentRow, 41] = qas[d][p][q].questionSAT3;
sheet1ValueArray[currentRow, 42] = qas[d][p][q].questionLAT3;
sheet1ValueArray[currentRow, 55] = currentParaNum;
currentRow++;
}
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 = 43;
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);
continue;
}
//마지막 파일 ADD
sheet1ValueList.Add(sheet1ValueArray);
// 여러 sheetValueArray들을 각 작업량의 따라 나눠 하나로 통합
string[] separator = { "(", ")", "-", " " }; //제외할 요소들
int totalRowCount_sheet1 = 0;
int totalRowCount_sheet2 = 0;
for (int i = 0; i < fileIndex; i++)
{
string[] _temp = (string[])splitedFileName[i];
string[] splited = _temp[2].Split(separator, StringSplitOptions.RemoveEmptyEntries);
//sheet1 작업
int startIndex = Convert.ToInt32(splited[0]);
int endIndex = Convert.ToInt32(splited[1]);
int length = endIndex - startIndex + 1;
totalRowCount_sheet1 += length;
int rowIndex_sheet1 = 0;
int rowIndex_sheet2 = 0;
object[,] temp_arrList = (object[, ])sheet1ValueList[i];
object[,] tempSheet1Value = new object[length, sheet1ColCount];
for (int j = startIndex - 1; j < endIndex; j++)
{
for (int k = 0; k < sheet1ColCount; k++)
{
tempSheet1Value[rowIndex_sheet1, k] = temp_arrList[j, k];
}
rowIndex_sheet1++;
}
/*
* //sheet2 작업
* startIndex = (int)tempSheet1Value[0, 37];
* endIndex = (int)tempSheet1Value[rowIndex_sheet1 - 1, 37];
* length = endIndex - startIndex + 1;
*
* totalRowCount_sheet2 += length;
*
* temp_arrList = (object[,])sheet2ValueList[i];
* object[,] tempSheet2Value = new object[length, sheet2ColCount];
*
* for (int j = startIndex - 1; j < endIndex; j++)
* {
* for (int k = 0; k < sheet2ColCount; k++)
* tempSheet2Value[rowIndex_sheet2, k] = temp_arrList[j, k];
* rowIndex_sheet2++;
* }
* sheet1ValueList.RemoveAt(i);
* sheet2ValueList.RemoveAt(i);
* sheet1ValueList.Insert(i, tempSheet1Value);
* sheet2ValueList.Insert(i, tempSheet2Value);
*/
}
sheet1RowCount = totalRowCount_sheet1;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
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++;
}
}
//엑셀파일에 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);
Marshal.ReleaseComObject(range);
Excel.Range c1 = objWorksheet.Cells[2, 1];
range = objWorksheet.get_Range(c1);
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
// * sheet1 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing);
objWorksheet.Name = "CrossToEtri";
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];
range = objWorksheet.get_Range(c1);
range.Value = sheet1ValueArray;
Marshal.FinalReleaseComObject(c1);
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);
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 = sheet1ValueArray[2, 2] == null ? "" : sheet1ValueArray[2, 2].ToString();
EtopTag.creator = sheet1ValueArray[2, 3] == null ? "" : sheet1ValueArray[2, 3].ToString();
EtopTag.data = new List <object>();
// * topTag 객체 내의 Data 객체 리스트 입력
IList <object> titleList = new List <object>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
object tempTitle = sheet1ValueArray[r, 10];
if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음)
{
titleList.Add(tempTitle);
}
else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == ""
{
titleList.Add(tempTitle);
}
else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
{
continue;
}
if (!titleList.Contains(tempTitle))
{
titleList.Clear();
titleList.Add(tempTitle);
}
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 = sheet1ValueArray[2, 10];
List <ETRI_Data> tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
ETRI_Paragraphs tempParagraphs = new ETRI_Paragraphs();
tempParagraphs.context = sheet1ValueArray[r, 11] == null ? "" : sheet1ValueArray[r, 11].ToString();
tempParagraphs.context_en = sheet1ValueArray[r, 12] == null ? "" : sheet1ValueArray[r, 12].ToString();
tempParagraphs.context_tagged = sheet1ValueArray[r, 13] == null ? "" : sheet1ValueArray[r, 13].ToString();
tempParagraphs.qas = new List <object>();
if (sheet1ValueArray[r, 10] == null || sheet1ValueArray[r, 7].ToString() == "")
{
if (r != 2)
{
dataCount++;
}
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet1ValueArray[r, 10] == null ? "" : sheet1ValueArray[r, 7].ToString();
}
else if (sheet1ValueArray[r, 10].Equals(currentTitle))
{
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
}
else
{
dataCount++;
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet1ValueArray[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>();
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 + "오류 이유:" + e.ToString());
}
}
return("모든 파일 변환 성공");
}
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();
}
// Main conversion routine
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Object activeSheet = null;
Window activeWindow = null;
Windows wbWin = null;
Hashtable templatePageSetup = new Hashtable();
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
// Excel can be very slow to start up, so try to get the COM
// object a few times
int tries = 10;
app = new Microsoft.Office.Interop.Excel.Application();
while (tries > 0)
{
try
{
// Try to set a property on the object
app.ScreenUpdating = false;
}
catch (COMException)
{
// Decrement the number of tries and have a bit of a snooze
tries--;
Thread.Sleep(500);
continue;
}
// Looks ok, so bail out of the loop
break;
}
if (tries == 0)
{
ReleaseCOMObject(app);
return((int)ExitCode.ApplicationError);
}
app.Visible = true;
app.DisplayAlerts = false;
app.AskToUpdateLinks = false;
app.AlertBeforeOverwriting = false;
app.EnableLargeOperationAlert = false;
app.Interactive = false;
app.FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone;
var onlyActiveSheet = (Boolean)options["excel_active_sheet"];
Boolean includeProps = !(Boolean)options["excludeprops"];
Boolean skipRecalculation = (Boolean)options["excel_no_recalculate"];
Boolean showHeadings = (Boolean)options["excel_show_headings"];
Boolean showFormulas = (Boolean)options["excel_show_formulas"];
Boolean isHidden = (Boolean)options["hidden"];
Boolean screenQuality = (Boolean)options["screen"];
Boolean updateLinks = !(Boolean)options["excel_no_link_update"];
int maxRows = (int)options[@"excel_max_rows"];
int worksheetNum = (int)options["excel_worksheet"];
int sheetForConversionIdx = 0;
activeWindow = app.ActiveWindow;
Sheets allSheets = null;
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if (isHidden)
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
// If we have no write password and we're attempting to open for writing, we might be
// caught out by an unexpected write password
if (writePassword == "" && !nowrite)
{
oWritePass = (Object)"FAKEPASSWORD";
try
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
catch (System.Runtime.InteropServices.COMException)
{
// Attempt to open it in read-only mode
workbook = workbooks.Open(inputFile, updateLinks, true, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
}
else
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
// Add in a delay to let Excel sort itself out
AddCOMDelay(options);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Get any template options
SetPageOptionsFromTemplate(app, workbooks, options, ref templatePageSetup);
// Get the sheets
allSheets = workbook.Sheets;
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
// We will save as xlsb (binary format) since this doesn't raise some errors when processing
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xlsb");
fmt = XlFileFormat.xlExcel12;
// Set up the print quality
if (screenQuality)
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// If a worksheet has been specified, try and use just the one
if (worksheetNum > 0)
{
// Force us just to use the active sheet
onlyActiveSheet = true;
try
{
if (worksheetNum > allSheets.Count)
{
// Sheet count is too big
return((int)ExitCode.WorksheetNotFound);
}
if (allSheets[worksheetNum] is _Worksheet)
{
((_Worksheet)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Worksheet)allSheets[worksheetNum]).Index;
}
else if (allSheets[worksheetNum] is _Chart)
{
((_Chart)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Chart)allSheets[worksheetNum]).Index;
}
}
catch (Exception)
{
return((int)ExitCode.WorksheetNotFound);
}
}
if (showFormulas)
{
// Determine whether to show formulas
try
{
activeWindow.DisplayFormulas = true;
}
catch (Exception) { }
}
// Keep the windows hidden
if (isHidden)
{
wbWin = workbook.Windows;
if (null != wbWin)
{
if (wbWin.Count > 0)
{
wbWin[1].Visible = false;
}
}
if (null != activeWindow)
{
activeWindow.Visible = false;
}
}
// Keep track of the active sheet
if (workbook.ActiveSheet != null)
{
activeSheet = workbook.ActiveSheet;
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (maxRows > 0 || showFormulas || showHeadings)
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
// Loop through all the sheets (worksheets and charts)
for (int wsIdx = 1; wsIdx <= allSheets.Count; wsIdx++)
{
var ws = allSheets.Item[wsIdx];
// Skip anything that is not the active sheet
if (onlyActiveSheet)
{
// Have to be careful to treat _Worksheet and _Chart items differently
try
{
int itemIndex = 1;
if (activeSheet is _Worksheet)
{
itemIndex = ((Worksheet)activeSheet).Index;
}
else if (activeSheet is _Chart)
{
itemIndex = ((Microsoft.Office.Interop.Excel.Chart)activeSheet).Index;
}
if (wsIdx != itemIndex)
{
ReleaseCOMObject(ws);
continue;
}
}
catch (Exception)
{
if (ws != null)
{
ReleaseCOMObject(ws);
}
continue;
}
sheetForConversionIdx = wsIdx;
}
if (showHeadings && ws is _Worksheet)
{
PageSetup pageSetup = null;
try
{
pageSetup = ((Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
}
catch (Exception) { }
finally
{
ReleaseCOMObject(pageSetup);
}
}
// If showing formulas, make things auto-fit
if (showFormulas && ws is _Worksheet)
{
Range cols = null;
try
{
((_Worksheet)ws).Activate();
activeWindow.DisplayFormulas = true;
cols = ((Worksheet)ws).Columns;
cols.AutoFit();
}
catch (Exception) { }
finally
{
ReleaseCOMObject(cols);
}
}
// If there is a maximum row count, make sure we check each worksheet
if (maxRows > 0 && ws is _Worksheet)
{
// Check for a print area
var pageSetup = ((Worksheet)ws).PageSetup;
var printArea = pageSetup.PrintArea;
ReleaseCOMObject(pageSetup);
if (string.IsNullOrEmpty(printArea))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > maxRows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Worksheet)ws).Name;
}
ReleaseCOMObject(cellSearch);
}
ReleaseCOMObject(cells);
}
ReleaseCOMObject(rows);
}
ReleaseCOMObject(range);
if (row_count > maxRows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
// Allow for re-calculation to be skipped
if (skipRecalculation)
{
app.Calculation = XlCalculation.xlCalculationManual;
app.CalculateBeforeSave = false;
}
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
if (onlyActiveSheet)
{
// Set up a delegate function for times we want to print
PrintDocument printFunc = delegate(string destination, string printer)
{
((Worksheet)activeSheet).PrintOut(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (sheetForConversionIdx > 0)
{
activeSheet = allSheets.Item[sheetForConversionIdx];
}
if (activeSheet is _Worksheet)
{
var wps = ((_Worksheet)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
((Worksheet)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
ReleaseCOMObject(wps);
}
else if (activeSheet is _Chart)
{
var wps = ((_Chart)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
((Microsoft.Office.Interop.Excel.Chart)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
ReleaseCOMObject(wps);
}
else
{
return((int)ExitCode.UnknownError);
}
AddCOMDelay(options);
}
else
{
PrintDocument printFunc = delegate(string destination, string printer)
{
workbook.PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (HasTemplateOption(options))
{
// Set up the template page setup options on all the worksheets
// in the workbook
var worksheets = workbook.Worksheets;
for (int wsIdx = 1; wsIdx <= worksheets.Count; wsIdx++)
{
var ws = worksheets[wsIdx];
var wps = (ws is _Worksheet) ? ((_Worksheet)ws).PageSetup : ((_Chart)ws).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
ReleaseCOMObject(wps);
ReleaseCOMObject(ws);
}
ReleaseCOMObject(worksheets);
}
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
}
ReleaseCOMObject(allSheets);
ReleaseCOMObject(fmt);
ReleaseCOMObject(quality);
return((int)ExitCode.Success);
}
catch (COMException ce)
{
if ((uint)ce.ErrorCode == 0x800A03EC)
{
return((int)ExitCode.EmptyWorksheet);
}
else
{
Console.WriteLine(ce.Message);
return((int)ExitCode.UnknownError);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
ReleaseCOMObject(activeSheet);
ReleaseCOMObject(activeWindow);
ReleaseCOMObject(wbWin);
GC.Collect();
GC.WaitForPendingFinalizers();
// Excel sometimes needs a bit of a delay before we close in order to
// let things get cleaned up
workbook.Saved = true;
CloseExcelWorkbook(workbook);
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
ReleaseCOMObject(workbook);
ReleaseCOMObject(workbooks);
ReleaseCOMObject(app);
GC.Collect();
GC.WaitForPendingFinalizers();
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
private string convert(string filePath)
{
m_path = filePath;
var missing = Type.Missing;
objApp = new Excel.Application();
objWorkbooks = objApp.Workbooks;
Cross_TopTag topTag;
Cross_Data[] data;
Cross_Paragraphs[][] paragraphs;
Cross_Qas[][][] qas;
Cross_Answers[][][][] answers;
object[,] sheet1ValueArray;
object[,] sheet2ValueArray;
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++;
}
}
// ** 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++;
}
}
}
// ** 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] = row + 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;
}
// * 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] = currentRow + 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++;
}
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파일: " + filePath);
}
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++;
}
}
}
// ** 엑셀로 출력
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 객체 데이터 입력
topTag = new Cross_TopTag();
topTag.version = sheet2ValueArray[2, 2] == null ? null : sheet2ValueArray[2, 2].ToString();
topTag.creator = sheet2ValueArray[2, 3] == null ? null : sheet2ValueArray[2, 3].ToString();
topTag.progress = Convert.ToInt32(sheet2ValueArray[2, 4]);
topTag.formatt = sheet2ValueArray[2, 5] == null ? null : sheet2ValueArray[2, 5].ToString();
topTag.time = Convert.ToDouble(sheet2ValueArray[2, 6]);
topTag.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, 7];
if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음)
{
titleList.Add(tempTitle);
}
else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == ""
{
titleList.Add(tempTitle);
}
else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
{
continue;
}
Cross_Data tempData = new Cross_Data();
tempData.title = tempTitle == null ? "" : tempTitle.ToString();
tempData.paragraphs = new List <object>();
topTag.data.Add(tempData);
}
// * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력
int dataCount = 0;
object currentTitle = sheet2ValueArray[2, 7];
List <Cross_Data> tempDataList = topTag.data.Cast <Cross_Data>().ToList();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
Cross_Paragraphs tempParagraphs = new Cross_Paragraphs();
tempParagraphs.context = sheet2ValueArray[r, 8] == null ? null : sheet2ValueArray[r, 8].ToString();
//tempParagraphs.context_original = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString();
tempParagraphs.context_en = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString();
tempParagraphs.context_tagged = sheet2ValueArray[r, 10] == null ? null : sheet2ValueArray[r, 10].ToString();
//if (sheet2ValueArray[r, 11] == null)
//{
// tempParagraphs.context_tagged = null;
//}
//else
//{
// //tempParagraphs.context_tagged = new List<string>();
// string[] tempTagged = sheet2ValueArray[r, 11].ToString().Split(':');
// foreach (var item in tempTagged)
// {
// tempParagraphs.context_tagged.Add(item);
// }
//}
tempParagraphs.qas = new List <object>();
if (sheet2ValueArray[r, 7] == null || sheet2ValueArray[r, 7].ToString() == "")
{
if (r != 2)
{
dataCount++;
}
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 7] == null ? null : sheet2ValueArray[r, 7].ToString();
}
else if (sheet2ValueArray[r, 7] == currentTitle)
{
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
}
else
{
dataCount++;
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 7].ToString();
}
}
topTag.data = tempDataList.Cast <object>().ToList();
// * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력
dataCount = 0;
int paragraphCount = 0;
int currentParagraph = 1;
tempDataList = topTag.data.Cast <Cross_Data>().ToList();
List <Cross_Qas> tempQasList = new List <Cross_Qas>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
Cross_Qas tempQas = new Cross_Qas();
tempQas.id = sheet1ValueArray[r, 2] == null ? null : sheet1ValueArray[r, 2].ToString();
tempQas.confuseQt1 = Convert.ToBoolean(sheet1ValueArray[r, 3] == null ? null : sheet1ValueArray[r, 3]);
tempQas.confuseQf1 = Convert.ToBoolean(sheet1ValueArray[r, 4] == null ? null : sheet1ValueArray[r, 4]);
tempQas.confuseSat1 = Convert.ToBoolean(sheet1ValueArray[r, 5] == null ? null : sheet1ValueArray[r, 5]);
tempQas.confuseLat1 = Convert.ToBoolean(sheet1ValueArray[r, 6] == null ? null : sheet1ValueArray[r, 6]);
tempQas.question = sheet1ValueArray[r, 7] == null ? null : sheet1ValueArray[r, 7].ToString();
tempQas.question_en = sheet1ValueArray[r, 8] == null ? null : sheet1ValueArray[r, 8].ToString();
tempQas.question_tagged1 = sheet1ValueArray[r, 9] == null ? null : sheet1ValueArray[r, 9].ToString();
tempQas.questionType1 = sheet1ValueArray[r, 10] == null ? null : sheet1ValueArray[r, 10].ToString();
tempQas.questionFocus1 = sheet1ValueArray[r, 11] == null ? null : sheet1ValueArray[r, 11].ToString();
tempQas.questionSAT1 = sheet1ValueArray[r, 12] == null ? null : sheet1ValueArray[r, 12].ToString();
tempQas.questionLAT1 = sheet1ValueArray[r, 13] == null ? null : sheet1ValueArray[r, 13].ToString();
tempQas.confuseQt2 = Convert.ToBoolean(sheet1ValueArray[r, 14] == null ? null : sheet1ValueArray[r, 14]);
tempQas.confuseQf2 = Convert.ToBoolean(sheet1ValueArray[r, 15] == null ? null : sheet1ValueArray[r, 15]);
tempQas.confuseSat2 = Convert.ToBoolean(sheet1ValueArray[r, 16] == null ? null : sheet1ValueArray[r, 16]);
tempQas.confuseLat2 = Convert.ToBoolean(sheet1ValueArray[r, 17] == null ? null : sheet1ValueArray[r, 17]);
tempQas.questionType2 = sheet1ValueArray[r, 18] == null ? null : sheet1ValueArray[r, 18].ToString();
tempQas.questionFocus2 = sheet1ValueArray[r, 19] == null ? null : sheet1ValueArray[r, 19].ToString();
tempQas.questionSAT2 = sheet1ValueArray[r, 20] == null ? null : sheet1ValueArray[r, 20].ToString();
tempQas.questionLAT2 = sheet1ValueArray[r, 21] == null ? null : sheet1ValueArray[r, 21].ToString();
tempQas.confuseQt3 = Convert.ToBoolean(sheet1ValueArray[r, 22] == null ? null : sheet1ValueArray[r, 22]);
tempQas.confuseQf3 = Convert.ToBoolean(sheet1ValueArray[r, 23] == null ? null : sheet1ValueArray[r, 23]);
tempQas.confuseSat3 = Convert.ToBoolean(sheet1ValueArray[r, 24] == null ? null : sheet1ValueArray[r, 24]);
tempQas.confuseLat3 = Convert.ToBoolean(sheet1ValueArray[r, 25] == null ? null : sheet1ValueArray[r, 25]);
tempQas.questionType3 = sheet1ValueArray[r, 26] == null ? null : sheet1ValueArray[r, 26].ToString();
tempQas.questionFocus3 = sheet1ValueArray[r, 27] == null ? null : sheet1ValueArray[r, 27].ToString();
tempQas.questionSAT3 = sheet1ValueArray[r, 28] == null ? null : sheet1ValueArray[r, 28].ToString();
tempQas.questionLAT3 = sheet1ValueArray[r, 29] == null ? null : sheet1ValueArray[r, 29].ToString();
List <Cross_Answers> tempAnswersList = new List <Cross_Answers>();
// * topTag->Data->Paragraphs->Qas 객체 리스트 내의 Answers 객체 리스트 입력
for (int i = 0; i < 3; i++)
{
int ansStartColNum = 22 + (i * 6);//18
if (sheet1ValueArray[r, ansStartColNum] == null)
{
break; // 정답의 text 공백이면 없음 처리
}
Cross_Answers tempAnswers = new Cross_Answers();
tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? null : sheet1ValueArray[r, ansStartColNum].ToString();
//tempAnswers.text_original = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].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();
//if (sheet1ValueArray[r, ansStartColNum + 3] == null)
//{
// tempAnswers.text_tagged = null;
//}
//else
//{
// tempAnswers.text_tagged = new List<string>();
// string[] tempTagged = sheet1ValueArray[r, ansStartColNum + 3].ToString().Split(':');
// foreach (var item in tempTagged)
// {
// tempAnswers.text_tagged.Add(item);
// }
//}
//if (sheet1ValueArray[r, ansStartColNum + 4] == null)
//{
// tempAnswers.text_syn = null;
//}
//else
//{
// tempAnswers.text_syn = new List<string>();
// string[] tempSyn = sheet1ValueArray[r, ansStartColNum + 4].ToString().Split(':');
// foreach (var item in tempSyn)
// {
// tempAnswers.text_syn.Add(item);
// }
//}
tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]);
tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]);
tempAnswersList.Add(tempAnswers);
}
tempQas.answers = tempAnswersList.Cast <object>().ToList();
tempQasList.Add(tempQas);
currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 40]); //36
if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때
{
if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 40])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면
{
topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입
tempQasList = new List <Cross_Qas>();
if (paragraphCount < topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리
{
paragraphCount++;
}
else // 도달하고 난 후에는 이렇게 처리
{
dataCount++;
paragraphCount = 0;
}
}
}
if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때
{
topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_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(topTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거
}
);
}
else
{
saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Include // Null값 객체 포함
}
);
}
using (StreamWriter sw = new StreamWriter(m_savePath))
{
sw.Write(saveJSONText);
}
#endregion
}
return(STR_CONVERTING_SUCCESS);
}
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파일: " + filePath);
}
}
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 static void ParseExcel(ref int numRowsOnPage, ref int maxPages, ref bool isParsed, List <Danger> dangers, List <string> changedDangers)
{
changedDangers.Clear();
List <Danger> OldDangers = new List <Danger>();
OldDangers.AddRange(dangers);
dangers.Clear();
string FileName = $"{Environment.CurrentDirectory}\\thrlist.xlsx";
if (!(new FileInfo(FileName).Exists))
{
throw new FileNotFoundException();
}
object rOnly = true;
object SaveChanges = false;
object MissingObj = System.Reflection.Missing.Value;
Excel.Application app = new Excel.Application();
Excel.Workbooks workbooks = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
try
{
workbooks = app.Workbooks;
workbook = workbooks.Open(FileName, MissingObj, rOnly, MissingObj, MissingObj,
MissingObj, MissingObj, MissingObj, MissingObj, MissingObj,
MissingObj, MissingObj, MissingObj, MissingObj, MissingObj);
sheets = workbook.Sheets;
foreach (Excel.Worksheet worksheet in sheets)
{
Excel.Range UsedRange = worksheet.UsedRange;
Excel.Range urRows = UsedRange.Rows;
Excel.Range urColums = UsedRange.Columns;
int RowsCount = urRows.Count;
int ColumnsCount = urColums.Count;
maxPages = RowsCount / numRowsOnPage + 1;
for (int i = 3; i <= RowsCount; i++)
{
string[] str = new string[ColumnsCount];
for (int j = 1; j <= ColumnsCount; j++)
{
Excel.Range CellRange = UsedRange.Cells[i, j];
string CellText = (CellRange == null || CellRange.Value2 == null) ? null : CellRange.Value.ToString();
if (CellText != null)
{
str[j - 1] = CellText;
}
}
Danger danger = new Danger(Int32.Parse(str[0]), str[1], str[2], str[3], str[4], str[5] == "1" ? true : false, str[6] == "1" ? true : false, str[7] == "1" ? true : false, DateTime.Parse(str[9]));
if (isParsed && !OldDangers.Contains(danger))
{
bool isNewRecord = true;
foreach (var item in OldDangers)
{
if (item.ID == danger.ID)
{
isNewRecord = false;
changedDangers.Add(item.GetChangedFields(danger));
}
}
if (isNewRecord)
{
changedDangers.Add(danger.ToString());
}
}
dangers.Add(danger);
}
isParsed = true;
if (urRows != null)
{
Marshal.ReleaseComObject(urRows);
}
if (urColums != null)
{
Marshal.ReleaseComObject(urColums);
}
if (UsedRange != null)
{
Marshal.ReleaseComObject(UsedRange);
}
if (worksheet != null)
{
Marshal.ReleaseComObject(worksheet);
}
}
}
catch (Exception)
{
maxPages = 0;
}
finally
{
if (sheets != null)
{
Marshal.ReleaseComObject(sheets);
}
if (workbook != null)
{
workbook.Close(SaveChanges);
Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
if (app != null)
{
app.Quit();
Marshal.ReleaseComObject(app);
app = null;
}
}
}