public Export(string embedded)
{
string tmp = String.Empty;
try
{
//получаем шаблон из прикладных ресурсов
Stream template = GetResourceFileStream(embedded);
//создаем временный файл
tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
//сохраняем шаблон во временный файл
using (var fileStream = File.Create(tmp))
{
template.CopyTo(fileStream);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Создаем приложение и открываем в нём временный файл
objApp = new Excel.Application();
objBook = objApp.Workbooks.Open(tmp);
objBook.Activate();
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
/// <summary>
/// Creates a spreadsheet in the give xls filename.
/// </summary>
/// <param name="filename">The complete filename with the absolute path.</param>
/// <param name="sheetname">The name of the sheet e.g. Hidden</param>
/// <returns>True if succeeded, false if failed.</returns>
public static bool createWorksheet(String filename, String sheetname, bool needsToBeHidden = false)
{
successStatus = false;
try
{
successStatus = openXlApp();
CurrentSpreadSheet css = CurrentSpreadSheet.Instance;
//checking if the call is being made for the currently open worbook. this is less expensive.
if ((css.CurrentWorkBook != null) && (css.CurrentWorkBook.FullName == filename))
{
xlSheets = css.CurrentWorkBook.Sheets as Excel.Sheets;
}
else
{
xlWorkbook = openXlWorkBook(filename);
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
}
xlSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlSheets.Count + 1]);
xlSheet.Name = sheetname;
if (needsToBeHidden) xlSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
xlWorkbook.Save();
successStatus = quitXlApp();
}
finally
{
garbageCollect();
}
return successStatus;
}
public void readExcel()
{
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Select the range of data containing the addresses and get the outer boundaries.
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
// Iterate through the data and concatenate the values into a comma-delimited string.
for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
{
for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
{
//Write the next value into the string.
Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
string cellvalue = cell.Value.ToString();
//TODO: add your business logic for retrieve cell value
}
}
}
public Export()
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
//for writing
public ExcelOperator()
{
ExcelApp = new Excel.Application();
workbooks = ExcelApp.Workbooks;
workbook = workbooks.Add();
sheets = workbook.Sheets;
worksheet = sheets[1];
worksheet.Select(Type.Missing);
directoryPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
fileName = "shift.xlsx";
}
public Workbook( Excel._Workbook oWB)
{
this.oWB = oWB;
this.workbookSheets = oWB.Sheets;
foreach (Excel._Worksheet openedWorkSheet in workbookSheets)
{
sheetCounter += 1;
Sheet openedSheet = new Sheet(openedWorkSheet);
setIndex(openedSheet);
Sheets.Add(openedSheet);
sheetDict[openedSheet] = openedWorkSheet;
}
this.ActiveSheet = Sheets[0];
}
public void OpenDocument(string name)
{
try
{
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(name);
this.filename = name;
xlWorksheets = xlWorkbook.Sheets;
xlWorksheet = xlWorksheets[1] as Excel.Worksheet; // set first worksheet by default
}
catch (Exception e)
{
LogError(e.Message, "OpenDocument");
Console.WriteLine(e.Message);
}
}
internal Workbook(
ExcelApplication excelApplication,
Excel.Workbook _workbook,
string filePath,
ObjectDisposedCallback disposeCallback)
{
ExcelApplication = excelApplication;
this._workbook = _workbook;
this.FilePath = filePath;
worksheets = new List<Worksheet>();
_sheets = _workbook.Worksheets;
worksheetDisposeCallback = sender => worksheets.Remove((Worksheet)sender);
this.disposeCallback = disposeCallback;
for (var i = 1; i <= _sheets.Count; i++)
{
worksheets.Add(new Worksheet(ExcelApplication, this, _sheets[i], worksheetDisposeCallback, false));
}
worksheets[0].Activate();
}
internal void UpdateLbSheets(ref ListBox lbWSheets)
{
lbWSheets.Items.Clear();
xlWSheets = xlWBook.Sheets;
foreach (Excel.Worksheet wsht in xlWSheets)
{
lbWSheets.Items.Add(wsht.Name);
}
int qry = 0;
try
{
qry = lbWSheets.Items.Cast<string>().Select((item, i) => new { Ite = item, index = i }).FirstOrDefault(it => Regex.IsMatch(it.Ite, ".*inventory.*", RegexOptions.IgnoreCase)).index;
}
catch (Exception) { }
lbWSheets.SetSelected(qry, true);
xlWsheet = xlWSheets[qry + 1];
}
public void WriteToExcel_hisDetail(DataTable thisTable, string FileName, string sheetName)
{
string strFilePath = FileName;
string XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\历史明细模板.xls";
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbooks wbks = app.Workbooks;
Excel._Workbook _wbk = wbks.Add(XLSName);
Excel.Sheets shs = _wbk.Sheets;
Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);
try
{
int sheetRowsCount = _wsh.UsedRange.Rows.Count;
int count = thisTable.Columns.Count;
//设置列名
//foreach (DataColumn myNewColumn in thisTable.Columns)
//{
// _wsh.Cells[0, count] = myNewColumn.ColumnName;
// count = count + 1;
//}er
//for (int i = 0; i < count; i++)
//{
// _wsh.Cells[1, i + 1] = thisTable.Columns[i].ColumnName;
//}
//加入內容
for (int i = 1; i <= thisTable.Rows.Count; i++)
{
int intBlockRowsNum = 0;
for (int j = 1; j <= thisTable.Columns.Count; j++)
{
int[] intBlockList = { 1, 2, 8, 9, 10, 14, 15, 16, 17, 18, 19 };
if (Array.IndexOf(intBlockList, j) < 0 && j != 7)
{
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = thisTable.Rows[i - 1][j - 1];
}
else if (j == 7)
{
switch ((int)thisTable.Rows[i - 1][j - 1])
{
case 1:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "人民币";
break;
case 2:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "美元";
break;
case 3:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "港币";
break;
case 4:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "澳元";
break;
case 5:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "新元";
break;
case 6:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "马币";
break;
case 7:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "英镑";
break;
case 8:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "欧元";
break;
case 9:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "日元";
break;
case 10:
_wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "台币";
break;
}
}
else
{
intBlockRowsNum++;
}
}
}
_wsh.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
_wsh.Cells.Columns.AutoFit();
_wsh.Cells.Rows.AutoFit();
//若為EXCEL2000, 將最後一個參數拿掉即可
_wbk.SaveAs(strFilePath, Excel.XlFileFormat.xlWorkbookNormal,
null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
false, false, null, null, null);
}
catch (Exception ex)
{
}
finally
{
//關閉文件
_wbk.Close(false, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_wsh);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk);
_wsh = null;
_wbk = null;
app = null;
}
}
public void WriteToExcel(DataTable thisTable, string FileName, string sheetName)
{
string strFilePath = FileName;
string XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\报告模板.xls";
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbooks wbks = app.Workbooks;
Excel._Workbook _wbk = wbks.Add(XLSName);
Excel.Sheets shs = _wbk.Sheets;
Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);
try
{
int sheetRowsCount = _wsh.UsedRange.Rows.Count;
int count = thisTable.Columns.Count;
//设置列名
//foreach (DataColumn myNewColumn in thisTable.Columns)
//{
// _wsh.Cells[0, count] = myNewColumn.ColumnName;
// count = count + 1;
//}er
for (int i = 0; i < count; i++)
{
_wsh.Cells[1, i + 1] = thisTable.Columns[i].ColumnName;
}
//加入內容
for (int i = 1; i <= thisTable.Rows.Count; i++)
{
for (int j = 1; j <= thisTable.Columns.Count; j++)
{
_wsh.Cells[i + sheetRowsCount, j] = thisTable.Rows[i - 1][j - 1];
}
}
_wsh.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
_wsh.Cells.Columns.AutoFit();
_wsh.Cells.Rows.AutoFit();
//若為EXCEL2000, 將最後一個參數拿掉即可
_wbk.SaveAs(strFilePath, Excel.XlFileFormat.xlWorkbookNormal,
null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
false, false, null, null, null);
}
catch (Exception ex)
{
}
finally
{
//關閉文件
_wbk.Close(false, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_wsh);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk);
_wsh = null;
_wbk = null;
app = null;
}
}
public MockWorkbook()
{
// worksheet indices; watch out! the second index here is the NUMBER of elements, NOT the max value!
var e = Enumerable.Range(1, 10);
// new Excel instance
app = new Excel.Application();
// create new workbook
wb = app.Workbooks.Add();
// get a reference to the worksheet array
// By default, workbooks have three blank worksheets.
ws = wb.Worksheets;
// add some worksheets
foreach (int i in e)
{
ws.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
public Form1()
{
InitializeComponent();
double x, y, d, dx, dy, x0, y0;
int y_step;
excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 3;
excelapp.Workbooks.Add(Type.Missing);
excelapp.DisplayAlerts = true;
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelappworkbooks[1];
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelcells_a1 = excelworksheet.get_Range("A1", Type.Missing);
excelcells = excelcells_a1.get_Offset(0, 0);
///////////////////////////
////// Источники ///////
///////////////////////////
double sourceRadius = 0.2;
sources[0].id = 0;
sources[0].x = 0.27;
sources[0].y = 2.00;
sources[0].radius = sourceRadius;
sources[1].id = 1;
sources[1].x = 1.10;
sources[1].y = 2.81;
sources[1].radius = sourceRadius;
sources[2].id = 2;
sources[2].x = 2.22;
sources[2].y = 2.64;
sources[2].radius = sourceRadius;
sources[3].id = 3;
sources[3].x = 2.80;
sources[3].y = 1.61;
sources[3].radius = sourceRadius;
sources[4].id = 4;
sources[4].x = 2.39;
sources[4].y = 0.51;
sources[4].radius = sourceRadius;
sources[5].id = 5;
sources[5].x = 1.30;
sources[5].y = 0.13;
sources[5].radius = sourceRadius;
sources[6].id = 6;
sources[6].x = 0.36;
sources[6].y = 0.81;
sources[6].radius = sourceRadius;
sources[7].id = 7;
sources[7].x = 1.49;
sources[7].y = 1.50;
sources[7].radius = sourceRadius;
///////////////////////////
////// Приёмники ///////
///////////////////////////
/*
// Вариант один к одному для ВОСЬМИ приёмников
double receiverRadius = 0.23;
double receiverInnerRadius = 0.13;
receiversCount = 8;
receivers[0].id = 0;
receivers[0].x = 0.23;
receivers[0].y = 1.97;
receivers[0].radius = receiverRadius;
receivers[0].innerRadius = receiverInnerRadius;
receivers[1].id = 1;
receivers[1].x = 1.08;
receivers[1].y = 2.76;
receivers[1].radius = receiverRadius;
receivers[1].innerRadius = receiverInnerRadius;
receivers[2].id = 2;
receivers[2].x = 2.23;
receivers[2].y = 2.62;
receivers[2].radius = receiverRadius;
receivers[2].innerRadius = receiverInnerRadius;
receivers[3].id = 3;
receivers[3].x = 2.82;
receivers[3].y = 1.63;
receivers[3].radius = receiverRadius;
receivers[3].innerRadius = receiverInnerRadius;
receivers[4].id = 4;
receivers[4].x = 2.44;
receivers[4].y = 0.54;
receivers[4].radius = receiverRadius;
receivers[4].innerRadius = receiverInnerRadius;
receivers[5].id = 5;
receivers[5].x = 1.31;
receivers[5].y = 0.2;
receivers[5].radius = receiverRadius;
receivers[5].innerRadius = receiverInnerRadius;
receivers[6].id = 6;
receivers[6].x = 0.35;
receivers[6].y = 0.81;
receivers[6].radius = receiverRadius;
receivers[6].innerRadius = receiverInnerRadius;
receivers[7].id = 7;
receivers[7].x = 1.49;
receivers[7].y = 1.50;
receivers[7].radius = receiverRadius;
receivers[7].innerRadius = receiverInnerRadius;
*/
// Полтное гексагональное покрытие приёмниками
x0 = 1.5; // Центр шины - x
y0 = 1.5; // Центр шины - y
/*
dx = 0.46; // Шаг по x для гексагональной разметки
dy = 0.42; // Шаг по y для гексагональной разметки
double receiverRadius = 0.23;
double receiverInnerRadius = 0.0;
*/
dx = 0.46; // Шаг по x для гексагональной разметки
dy = 0.42; // Шаг по y для гексагональной разметки
double receiverRadius = 0.20;
double receiverInnerRadius = 0.10;
y_step = 0;
y = 0;
receiversCount = 0;
while (y < busRadius * 2)
{
// Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
if (y_step % 2 == 0)
{
x = 0;
}
else
{
x = dx / 2;
}
while (x < busRadius * 2)
{
d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
if (d < busRadius)
{
// Канал в шине (точка в круге)
receivers[receiversCount].x = x;
receivers[receiversCount].y = y;
receivers[receiversCount].radius = receiverRadius;
receivers[receiversCount].innerRadius = receiverInnerRadius;
receiversCount++;
}
x = x + dx;
}
y = y + dy;
y_step++;
}
////////////////////////
////// Каналы ///////
////////////////////////
dx = 0.0429; // Шаг по x для гексагональной разметки
dy = 0.0357; // Шаг по y для гексагональной разметки
x0 = 1.5; // Центр шины - x
y0 = 1.5; // Центр шины - y
double channelRaduis = 0.021; // Радиус одного канала
y_step = 0;
y = 0;
while (y < busRadius * 2)
{
// Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
if (y_step % 2 == 0)
{
x = 0;
}
else
{
x = dx / 2;
}
while (x < busRadius * 2)
{
d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
if (d < busRadius)
{
// Канал в шине (точка в круге)
channels[channelsCount].x = x;
channels[channelsCount].y = y;
channels[channelsCount].radius = channelRaduis;
channels[channelsCount].isOk = true;
channelsCount++;
}
x = x + dx;
}
y = y + dy;
y_step++;
}
}
// Event Handler for the middle browse button.
private void mBook2Load_Click(object sender, EventArgs e)
{
// Create a new OpenFileDialog object that will be used to select the second
// workbook.
System.Windows.Forms.OpenFileDialog fWin = new OpenFileDialog();
// Set the extensions and default folder path to be used by the dialog.
fWin.DefaultExt = "xlsx";
fWin.Filter = "Spreadsheet Files (*.xlsx;*.xls;*.csv)|*.xlsx;*.xls;*.csv";
fWin.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
// Open the dialog and do addional processing if a valid result is returned.
if (fWin.ShowDialog() == DialogResult.OK)
{
// If a workbook is currently open close it.
mBook2Sheets = null;
if (mBook2 != null)
{
mBook2.Close(false);
mBook2 = null;
}
if (fWin.FileName.Equals(mBook1File.Text) && fWin.FileName.EndsWith(".csv"))
{
MessageBox.Show("Cannot compare a csv file to itself.");
return;
}
// Open the workbook selected in the dialog.
mBook2File.Text = fWin.FileName;
mBook2 = (Excel._Workbook)(mExcel.Workbooks.Add(fWin.FileName));
mBook2Sheets = (Excel.Sheets)(mBook2.Sheets);
// Enable and clear the combobox for the second workbook's worksheets.
mBook2SheetBox.Enabled = true;
mBook2SheetBox.Items.Clear();
// Populate the combobox for the second workbook.
int numSheets = mBook2Sheets.Count;
for (int i = 1; i <= numSheets; i++)
{
mBook2SheetBox.Items.Add(((Excel._Worksheet)(mBook2Sheets.Item[i])).Name);
}
}
}
private void CreateInvoice(List <RentReportDto> rentList, string templateFilePath)
{
try
{
string templatePath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), templateFilePath);
Excel.Application xlApp = null;
xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(templatePath);
Excel.Sheets xlSheets = xlWorkbook.Sheets;
Excel.Worksheet xlWorksheet = xlSheets[1];
//Make a copy
xlWorksheet.Copy();
int row = 4;
foreach (RentReportDto rent in rentList)
{
++row;
int column = 0;
xlWorksheet.Cells[row, ++column] = rent.BuildingName + " " + rent.ArabicName;
xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].Merge(Type.Missing);
xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].Font.Bold = true;
xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].Font.Size = 20;
row++;
foreach (ContractDto contract in rent.contractDto)
{
column = 0;
xlWorksheet.Cells[row, ++column] = row + 1;
xlWorksheet.Cells[row, ++column] = contract.SpaceunitCombinedName;
xlWorksheet.Cells[row, ++column] = contract.ContractorName;
xlWorksheet.Cells[row, ++column] = contract.ContractStartDate;
xlWorksheet.Cells[row, ++column] = contract.ContractEndDate;
xlWorksheet.Cells[row, ++column] = contract.ContractAmount;
xlWorksheet.Cells[row, ++column] = contract.NoOfInstallments;
foreach (ContractPayment payment in contract.Contractpayments)
{
xlWorksheet.Cells[row, ++column] = payment.Amount;
xlWorksheet.Cells[row, ++column] = payment.DueDate.Value.ToString("MM/dd/yyyy");
xlWorksheet.Cells[row, ++column] = payment.Status;
}
row++;
}
}
//var activeWorkbook = ((Excel.Workbook) Application.ActiveWorkbook);
string invoicePath = System.IO.Path.GetDirectoryName(templatePath);
string file = string.Format(@"{0}\Invoice_{1}.xlsx", "output", DateTime.Now.Year + "-" + DateTime.Now.Month + "-" +
DateTime.Now.Day);
invoicePath = System.IO.Path.Combine(invoicePath, file);
if (File.Exists(invoicePath))
{
File.Delete(invoicePath);
}
if (!Directory.Exists(Path.GetDirectoryName(invoicePath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(invoicePath));
}
xlWorksheet.SaveAs(invoicePath);
xlWorkbook.Close();
// ...clean up...
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorkbooks);
//Kill the provesds as Excel process is stubbon
if (xlApp != null)
{
xlApp.Quit();
int xIProcessId = 0;
GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), ref xIProcessId);
Process xlProc = Process.GetProcessById(xIProcessId);
if (xlProc != null)
{
xlProc.Kill();
}
}
Process.Start(invoicePath);
}
catch (Exception ex)
{
LogWriter.Write(ex);
}
}
public void ToExcel(List <Pesertaujian> PesertaUjian, Pesertaujian JadwalPeserta)
{
// ------------ to excel ---------------------
// build excel apps
Excel.Application app = null;
Excel.Workbooks books = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
// create apps object
var excelApp = new Excel.Application();
excelApp.DefaultSaveFormat = Excel.XlFileFormat.xlOpenXMLWorkbook;
try
{
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.Workbooks.Add();
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// text keterangan
workSheet.Cells[1, "A"] = "DAFTAR PESERTA UJIAN " + JadwalPeserta.JenisUjian.ToUpper();
workSheet.Cells[3, "A"] = "Mata Kuliah/Kelas";
workSheet.Cells[4, "A"] = "Tahun/Semester";
workSheet.Cells[5, "A"] = "Jadwal";
workSheet.Cells[6, "A"] = "Dosen Pengampu";
workSheet.Cells[3, "C"] = JadwalPeserta.Makul + "/" + JadwalPeserta.Kelas;
workSheet.Cells[4, "C"] = JadwalPeserta.Tahun + "/" + JadwalPeserta.Semester;;
workSheet.Cells[5, "C"] = JadwalPeserta.Jadwal;
workSheet.Cells[6, "C"] = JadwalPeserta.Dosen;
workSheet.Cells[8, "A"] = "NO";
workSheet.Cells[8, "B"] = "NPM";
workSheet.Cells[8, "C"] = "NAMA";
workSheet.Cells[8, "D"] = "NILAI ANGKA";
workSheet.Cells[8, "E"] = "NILAI HURUF";
workSheet.Cells[8, "F"] = "TANDA TANGAN";
// loop
var row = 8;
foreach (var mhs in PesertaUjian)
{
row++;
workSheet.Cells[row, "A"] = "";
workSheet.Cells[row, "B"] = mhs.npm;
workSheet.Cells[row, "C"] = mhs.nama;
}
//tanda tangan
workSheet.Cells[row + 3, "B"] = "Ketua Jurusan";
workSheet.Cells[row + 7, "B"] = "...";
workSheet.Cells[row + 2, "E"] = "Magelang, ...";
workSheet.Cells[row + 3, "E"] = "Dosen Pengampu,";
workSheet.Cells[row + 7, "E"] = JadwalPeserta.Dosen.Trim();
// autofit
workSheet.Columns[1].ColumnWidth = 3;
workSheet.Columns[2].Autofit();
workSheet.Columns[3].Autofit();
workSheet.Columns[4].Autofit();
//workSheet.Columns[5].Autofit();
workSheet.Columns[6].Autofit();
// text align left
workSheet.get_Range("A1", "D" + row.ToString()).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
// border nilai
var range = workSheet.get_Range("A8", "F" + row.ToString());
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
// save excel file
//string excelFile = "C:\\PesertaUjian\\" + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim();
string excelFile = Server.MapPath("~/PesertaUjian/") + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim();
workSheet.SaveAs(excelFile, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
// quit excel apps
excelApp.Workbooks.Close();
excelApp.Quit();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
// quit excel apps
excelApp.Workbooks.Close();
excelApp.Quit();
throw ex;
}
finally
{
// release excel object
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
if (book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
if (books != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
try
{
// FileInfo fileInfo = new FileInfo(Server.MapPath("~/Nilai/coba.xlsx"));
// C:\inetpub\wwwroot\portal
// FileInfo fileInfo = new FileInfo("C:\\PesertaUjian\\" + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim() + ".xlsx");
FileInfo fileInfo = new FileInfo(Server.MapPath("~/PesertaUjian/") + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim() + ".xlsx");
Response.Clear();
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileInfo.Name));//"attachment;filename=" + fileInfo.Name);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.Flush();
Response.WriteFile(fileInfo.FullName);
Response.End();
// kill excel proccess
Process[] pProcess;
pProcess = Process.GetProcessesByName("Excel");
pProcess[0].Kill();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
throw ex;
}
}
public static void ExportToExcel(string fileName, string startDate, string EndDate)
{
B2CLInvoiceGenerate(startDate, EndDate);
Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.DisplayAlerts = false;
string filePath = fileName;
Excel.Workbook xlWorkBook = oXL.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets worksheets = xlWorkBook.Worksheets;
Excel.Range oRng;
var oSheet = (Excel.Worksheet)worksheets.Add(worksheets[oXL.ActiveWorkbook.Worksheets.Count], Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "B2CL";
#region Content
int i = 0;
int j = 0;
for (i = 0; i <= mDt.Rows.Count - 1; i++)
{
for (j = 0; j <= mDt.Columns.Count - 1; j++)
{
oSheet.Cells[i + 1, j + 1] = mDt.Rows[i][j];// cell.Value;
}
}
oXL.StandardFont = "Cambria";
oSheet.get_Range("A1", "A1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(((int)(((byte)(66)))), ((int)(((byte)(113)))), ((int)(((byte)(244))))));
oSheet.get_Range("A2", "H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(((int)(((byte)(66)))), ((int)(((byte)(113)))), ((int)(((byte)(244))))));
oSheet.get_Range("A1", "H2").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
oSheet.get_Range("A4", "H4").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Bisque);
oSheet.get_Range("C1", "C" + (mDt.Rows.Count + 1)).NumberFormat = "#,###,###0.00";
oSheet.get_Range("E1", "G" + (mDt.Rows.Count + 1)).NumberFormat = "#,###,###0.00";
oSheet.get_Range("A1", "H" + (mDt.Rows.Count + 1)).Font.FontStyle = "Cambria";
oSheet.get_Range("A1", "H2").Font.Bold = true;
oSheet.get_Range("A1", "H2").Font.Size = 12;
oSheet.get_Range("A1", "B" + (mDt.Rows.Count + 1)).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "B" + (mDt.Rows.Count + 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//BorderLine
oSheet.get_Range("A1", "H3").Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
oRng = oSheet.get_Range("A1", "H3");
oRng.EntireColumn.AutoFit();
#endregion
oSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
oSheet.Select();
xlWorkBook.Save();
xlWorkBook.Close();
releaseObject(oSheet);
releaseObject(worksheets);
releaseObject(xlWorkBook);
releaseObject(oXL);
}
public static void ExportDataGridViewTo_Excel12(DataGridView myDataGridView)
{
try
{
Excel_12.Application oExcel_12 = null; //Excel_12 Application
Excel_12.Workbook oBook = null; // Excel_12 Workbook
Excel_12.Sheets oSheetsColl = null; // Excel_12 Worksheets collection
Excel_12.Worksheet oSheet = null; // Excel_12 Worksheet
Excel_12.Range oRange = null; // Cell or Range in worksheet
Object oMissing = System.Reflection.Missing.Value;
// Create an instance of Excel_12.
oExcel_12 = new Excel_12.Application();
// Make Excel_12 visible to the user.
oExcel_12.Visible = true;
// Set the UserControl property so Excel_12 won't shut down.
oExcel_12.UserControl = true;
// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
// Add a workbook.
oBook = oExcel_12.Workbooks.Add(oMissing);
// Get worksheets collection
oSheetsColl = oExcel_12.Worksheets;
// Get Worksheet "Sheet1"
oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");
// Export titles
for (int j = 0; j < myDataGridView.Columns.Count; j++)
{
oRange = (Excel_12.Range)oSheet.Cells[1, j + 1];
oRange.Value2 = myDataGridView.Columns[j].HeaderText;
}
// Export data
for (int i = 0; i < myDataGridView.Rows.Count; i++)
{
for (int j = 0; j < myDataGridView.Columns.Count; j++)
{
oRange = (Excel_12.Range)oSheet.Cells[i + 2, j + 1];
oRange.Value2 = myDataGridView[j, i].Value.ToString();
}
}
// Release the variables.
//oBook.Close(false, oMissing, oMissing);
oBook = null;
//oExcel_12.Quit();
oExcel_12 = null;
// Collect garbage.
GC.Collect();
}
catch
{
MessageBox.Show("CAN NOT GENERATE EXCEL FILE", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
// Instantiate Excel and start a new workbook.
ArrayList exInst = new ArrayList(); //List of existing Excel
Process[] P_CESSES = Process.GetProcessesByName("EXCEL");
for (int p_count = 0; p_count < P_CESSES.Length; p_count++)
{
exInst.Add(P_CESSES[p_count].Id);
}
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Open("Y:\\Mechanical\\Inventor\\iLogic_Plug_Ins\\iLogic_Drawings_Plug_Ins\\TestXL.xlsx");
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
objSheet.Cells[2, 1] = "Heyy";
Boolean bEndFound = false;
int n = 0;
String tempString;
int tempCount = 0;
if (cats == null)
{
cats = new Dictionary <string, int>();
}
else
{
cats.Clear();
}
ComboBox _cbCats = cbCats;
while (!bEndFound)
{
objRange = ((Excel.Range)objSheet.Cells[3 + n, 4]);
tempString = objRange.Text;
if (tempString.Equals(""))
{
bEndFound = true;
}
else
{
objRange = ((Excel.Range)objSheet.Cells[3 + n, 5]);
tempCount = Convert.ToInt32(objRange.Value2);
cats.Add(tempString, tempCount);
n++;
_cbCats.Items.Add(tempString);
}
}
objBook.Save();
objBook.Close(0);
Boolean bFound = false;
P_CESSES = Process.GetProcessesByName("EXCEL");
for (int p_count = 0; p_count < P_CESSES.Length; p_count++)
{
bFound = false;
foreach (int s in exInst)
{
if (s.Equals(P_CESSES[p_count].Id))
{
bFound = true;
}
}
if (!bFound)
{
P_CESSES[p_count].Kill();
}
}
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objApp) != 0)
{
}
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBooks) != 0)
{
}
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBook) != 0)
{
}
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheets) != 0)
{
}
while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheet) != 0)
{
}
objApp = null;
objBooks = null;
objBook = null;
objSheets = null;
objSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (Exception myError)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, myError.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, myError.Source);
MessageBox.Show(errorMessage, "Error");
}
}
private void exportexcel(System.Data.DataTable dt, string filename)
{
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel.Series oSeries;
// Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel._Chart oChart;
//String sMsg;
//int iNumQtrs;
GC.Collect();//系统的垃圾回收
//string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
//Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);
ep.Visible = true;
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
ws.UsedRange.Select();
ws.UsedRange.Copy(System.Type.Missing);
// wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[rowIndex, colIndex] = col.ColumnName;
colIndex++;
}
for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
}
oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[30, 30]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
XlRowCol.xlColumns, true, true, true,
this.bandedGridView1.GroupPanelText, Missing.Value, Missing.Value, Missing.Value);
oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);
oChart.PlotVisibleOnly = false;
// oChart.HasDataTable = true;
Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//ax.HasTitle = true;
//ax.AxisTitle.Text = "Sales Figures";
ax.HasMajorGridlines = true;
//string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
private bool read_file(out string msg)
{
msg = null;
Excel._Application app = new Excel.Application();
try
{
// string FilePath = Path.Combine(inpath, FileName);
Excel._Workbook book = app.Workbooks.Open(FilePath, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel._Worksheet sheet;
sheet = (Excel._Worksheet)sheets.get_Item(1);
Excel.Range range;
data = new List <STRowUpdateContract>();
int lastRow = sheet.UsedRange.Rows.Count;
for (int i = 1; i <= lastRow; i++)
{
range = sheet.get_Range(string.Format("A{0}", i), Missing.Value);
if (range.Value2 == null)
{
continue;
}
string s = Convert.ToString(range.Value2).Trim();
if (!is_d(s))
{
continue;
}
else
{
STRowUpdateContract item = new STRowUpdateContract();
range = sheet.get_Range(string.Format("A{0}", i), Missing.Value);
if (range.Value2 != null)
{
item.contrcode = Convert.ToInt32(range.Value2);
}
else
{
item.contrcode = -1;
}
range = sheet.get_Range(string.Format("B{0}", i), Missing.Value);
if (range.Value2 != null)
{
item.mdmcode = Convert.ToString(range.Value2).Trim();
}
else
{
item.mdmcode = null;
}
if (item.contrcode > 0 && !string.IsNullOrEmpty(item.mdmcode))
{
data.Add(item);
}
}
}
app.Quit();
}
catch (Exception ex)
{
msg = ex.Message;
log.LogLine("File wasn't read.");
return(false);
}
finally { app.Quit(); }
return(true);
}
/// <summary>
/// 持续打开Excel文件
/// </summary>
/// <returns></returns>
public bool OpenFileContinuously()
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Open(FileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(SheetNumber);//读取一张表
iRowCount = worksheet.UsedRange.Rows.Count;
iColumnCount = worksheet.UsedRange.Columns.Count;
booFileOpenState = true;
return true;
}
catch (Exception ex)
{
strErrorString = ex.Message;
return false;
}
}
private void cmbJoblist_SelectedIndexChanged(object sender, EventArgs e)
{
Excel.Workbook excelWorkbook = null;
rd = null;
resDB = null;
GOSTOP = false;
btnCalc.Enabled = false;
try
{
txtTpn.Text = "";
txtTpsn.Text = "";
txtPosn.Text = "";
string str = txtCurDir.Text + "\\" + cmbJoblist.SelectedItem.ToString();
if (!File.Exists(str))
{
MessageBox.Show("File is not found, " + str, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Read File and display the file header.
Excel.Application excelApp = new Excel.Application(); // Creates a new Excel Application
excelApp.Visible = false; // Makes Excel visible to the user.
// The following code opens an existing workbook
string workbookPath = str; // Add your own path here
try
{
excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
}
catch (Exception ex)
{
MessageBox.Show("File open Error: " + ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// The following gets the Worksheets collection
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
// The following gets Sheet1 for editing
string currentSheet = "RawDB";
Excel.Worksheet excelWorksheet = null;
try
{
excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("RawDB");
}
catch
{
try
{
excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("Raw DB");
}
catch
{
MessageBox.Show("Error: There is not the Sheet that is named 'Raw DB'", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (excelWorkbook != null)
{
excelWorkbook.Close();
}
return;
}
}
// The following gets cell A1 for editing
int tpn, tpsn, posn, RawStart, ColStart;
try
{
tpn = (int)excelWorksheet.Cells[1, 2].Value2;
Common.TPN = tpn;
txtTpn.Text = tpn.ToString();
tpsn = (int)excelWorksheet.Cells[2, 2].Value2;
Common.TSPN = tpsn;
txtTpsn.Text = tpsn.ToString();
posn = (int)excelWorksheet.Cells[3, 2].Value2;
Common.POSN = posn;
txtPosn.Text = posn.ToString();
RawStart = (int)excelWorksheet.Cells[4, 2].Value2;
ColStart = (int)excelWorksheet.Cells[4, 3].Value2;
txtStart.Text = "Row:" + RawStart.ToString() + ",Col:" + ColStart.ToString();
}
catch (Exception ex)
{
MessageBox.Show("Data Read Error, please check the input data(TPN,TPSN,POSN,Start Position) in 'RAW DB' sheet.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (excelWorkbook != null)
{
excelWorkbook.Close();
}
return;
}
// Read All Data and make a RawData Class
rd = new RawData(posn, tpn, tpsn);
int d = 0;
for (int i = 0; i < posn; i++)
{
for (int j = 0; j < tpn; j++)
{
rd.Set(i, j, 0);
}
for (int j = 0; j < tpn; j++)
{
try
{
d = Convert.ToInt32(excelWorksheet.Cells[RawStart + i, ColStart + j].Value2);
if (d > 0 && d <= tpn)
{
rd.Set(i, d - 1, 1);
}
}
catch
{
d = 0;
}
if (d == 0)
{
break;
}
}
if (i % 100 == 0)
{
Console.WriteLine("line: " + i.ToString());
}
}
excelWorkbook.Close();
excelWorkbook = null;
//rd.printRawData();
btnCalc.Enabled = true;
StatusProgress.Value = 0;
statusLabel1.Text = "";
timer1.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show("Error: unhandled error was raised. system message:" + ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (excelWorkbook != null)
{
excelWorkbook.Close();
}
}
}
//This method wirtes the sim data to the excel file.
public void WriteToExcel()
{
int iSimRunNumber = 0;
int linenumber = 0;
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = false;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
//Add a new sheets object.
oXLSheets = oXL.Sheets as Excel.Sheets;
foreach (DOE22SimFile simfile in DOESimFiles)
{
iSimRunNumber++;
oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "RUN-" + iSimRunNumber.ToString();
//oSheet.Name = Path.GetFileName(simfile.filepath);
linenumber = 0;
// Output BEPS to excel Sheet.
oSheet.Cells[linenumber = 1, 1] = Path.GetFileName(simfile.filepath);
linenumber++;
oSheet.Cells[linenumber, 1] = "BEPS";
linenumber++;
//print bpes report.
PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
linenumber++;
oSheet.Cells[linenumber, 1] = "ES-D";
linenumber++;
//Print es-d report.
PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[simfile.bepsTable.Rows.Count,
simfile.bepsTable.Columns.Count]);
oRange.EntireColumn.AutoFit();
}
//reset linenumber for All sheet.
linenumber = 0;
oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "ALL";
foreach (DOE22SimFile simfile in DOESimFiles)
{
linenumber++;
// Output Filename to excel Sheet.
oSheet.Cells[linenumber, 1] = Path.GetFileName(simfile.filepath);
linenumber++;
if (bWriteBEPS == true)
{
// Output Filename to excel Sheet.
oSheet.Cells[linenumber, 1] = "BEPS";
linenumber++;
//print beps report.
PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
}
//Print ES-D
if (bWriteESD == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "ES-D";
linenumber++;
//Print es-d report.
PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
linenumber = linenumber + simfile.esdTable.Rows.Count + 1;
}
//Print Zone Annual Data
if (bWriteZoneAnnualData == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "Zone Annual Data";
linenumber++;
//Print Zone Annual Data report.
PrintTableToExcel(linenumber, simfile.ZoneAnnualTable, oSheet);
linenumber = linenumber + simfile.ZoneAnnualTable.Rows.Count + 1;
}
//Print System Annual Data
if (bWriteSystemAnnualData == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "System Annual Data";
linenumber++;
//Print Zone Annual Data report.
PrintTableToExcel(linenumber, simfile.SystemAnnualTable, oSheet);
linenumber = linenumber + simfile.SystemAnnualTable.Rows.Count + 1;
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[simfile.bepsTable.Rows.Count,
simfile.bepsTable.Columns.Count]);
oRange.EntireColumn.AutoFit();
}
// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(sFoldername + @"\test.xls", Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void button4_Click(object sender, EventArgs e)
{
string FileName = textBox1.Text;
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;
workbooks = app.Workbooks;
workbook = workbooks.Open(FileName, MissingObj, rOnly, MissingObj, MissingObj,
MissingObj, MissingObj, MissingObj, MissingObj, MissingObj,
MissingObj, MissingObj, MissingObj, MissingObj, MissingObj);
sheets = workbook.Sheets;
Context db = new Context();
toolStripStatusLabel1.Text = "Очистка таблицы 1 БД";
var rows = from o in db.Table1
select o;
foreach (var row in rows)
{
db.Table1.Remove(row);
}
db.SaveChanges();
toolStripStatusLabel1.Text = "Очистка таблицы 1 БД завершена";
toolStripStatusLabel1.Text = "Очистка таблицы 2 БД";
var rows2 = from o in db.Table2
select o;
foreach (var row2 in rows2)
{
db.Table2.Remove(row2);
}
db.SaveChanges();
toolStripStatusLabel1.Text = "Очистка таблицы 2 БД завершена";
toolStripStatusLabel1.Text = "Загрузка данных в таблицу 1 БД";
foreach (Excel.Worksheet worksheet in sheets)
{
Excel.Range UsedRange = worksheet.UsedRange;
Excel.Range urRows = UsedRange.Rows;
int RowsCount = urRows.Count;
int step = RowsCount / 50;
int count = 0;
for (int i = 1; i <= RowsCount; i++)
{
Excel.Range CellRange = UsedRange.Cells[i, 1];
string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
(CellRange as Excel.Range).Value2.ToString();
if (CellText != null)
{
var table1 = new Table1
{
Number1 = CellText
};
db.Table1.Add(table1);
}
toolStripStatusLabel1.Text = $"В таблицу 1 БД из {RowsCount} записей загружено: {i}";
count++;
if (count == step)
{
progressBar1.PerformStep();
count = 0;
}
}
}
db.SaveChanges();
toolStripStatusLabel1.Text = "Загрузка данных в таблицу 1 БД завершена";
FileName = textBox2.Text;
app = new Excel.Application();
workbooks = null;
workbook = null;
sheets = null;
workbooks = app.Workbooks;
workbook = workbooks.Open(FileName, MissingObj, rOnly, MissingObj, MissingObj,
MissingObj, MissingObj, MissingObj, MissingObj, MissingObj,
MissingObj, MissingObj, MissingObj, MissingObj, MissingObj);
sheets = workbook.Sheets;
toolStripStatusLabel1.Text = "Загрузка данных в таблицу 2 БД";
foreach (Excel.Worksheet worksheet in sheets)
{
Excel.Range UsedRange = worksheet.UsedRange;
Excel.Range urRows = UsedRange.Rows;
int RowsCount = urRows.Count;
int step = RowsCount / 50;
int count = 0;
for (int i = 1; i <= RowsCount; i++)
{
Excel.Range CellRange = UsedRange.Cells[i, 1];
string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
(CellRange as Excel.Range).Value2.ToString();
if (CellText != null)
{
var table2 = new Table2
{
Number2 = CellText
};
db.Table2.Add(table2);
}
toolStripStatusLabel1.Text = $"В таблицу 2 БД из {RowsCount} записей загружено: {i}";
count++;
if (count == step)
{
progressBar1.PerformStep();
count = 0;
}
}
}
db.SaveChanges();
toolStripStatusLabel1.Text = "Загрузка данных в таблицу 2 БД завершена";
toolStripStatusLabel1.Text = "Выполняется запрос к БД";
var result = from c in db.Table1
join p in db.Table2 on c.Number1 equals p.Number2 into ps
from p in ps.DefaultIfEmpty()
select new { Number1 = c, Number2 = p == null ? c.Number1 : null };
toolStripStatusLabel1.Text = "Запрос к БД выполнен";
toolStripStatusLabel1.Text = "Вывод в файл";
StreamWriter f = new StreamWriter(textBox3.Text + @"\result.txt");
foreach (var p in result)
{
if (p.Number2 != null)
{
f.WriteLine(p.Number2);
Console.WriteLine(p.Number2);
}
}
f.Close();
toolStripStatusLabel1.Text = "Вывод в файл завершен";
toolStripStatusLabel1.Text = "Готово";
}
public List <string> GetWorkSheet(string fileName, int worksheetNumber, string rangeStart, string rangeEnd)
{
// ->>>>> int userRow, int userCol
// Reference to Excel Application.
Excel.Application app = null;
Excel.Workbooks books = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheet = null;
Excel.Range range = null;
string startupPath = System.IO.Directory.GetCurrentDirectory();
try
{
app = new Excel.Application();
app.DisplayAlerts = false;
books = app.Workbooks;
//book = books.Open(Path.GetFullPath(Path.GetFullPath(@"TestData\" + fileName + ".xlsx")));
book = books.Open(fileName);
sheets = book.Sheets;
sheet = sheets.get_Item(1);
range = sheet.get_Range(rangeStart, rangeEnd);
//range.NumberFormat = "General";
//range.Value2 = data;
object[,] cellValues = (object[, ])range.Value2;
//List<double> lst = cellValues.Cast<object>().ToList().ConvertAll(x=> Convert.ToDouble(x));
List <string> lst = cellValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x));
book.Close();
app.Quit();
//Console.WriteLine(lst[0]);
//Console.WriteLine(lst[1]);
//Console.WriteLine(lst[2]);
return(lst);
}
finally
{
if (range != null)
{
Marshal.ReleaseComObject(range);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
if (sheets != null)
{
Marshal.ReleaseComObject(sheets);
}
if (book != null)
{
Marshal.ReleaseComObject(book);
}
if (books != null)
{
Marshal.ReleaseComObject(books);
}
if (app != null)
{
Marshal.ReleaseComObject(app);
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
//Excel.Application xlApp = new Excel.Application();
//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(Path.GetFullPath(@"TestData\" + fileName + ".xlsx"));
//Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(worksheetNumber);
//Excel.Range xlRange = xlWorksheet.UsedRange;
//
//double[] valueArray = (double[])xlRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
/*
*
* // Cleanup
* xlWorkbook.Close(false);
* xlApp.Quit();
*
* // Manual disposal because of COM
* while (Marshal.ReleaseComObject(xlApp) != 0) { }
* while (Marshal.ReleaseComObject(xlWorkbook) != 0) { }
* while (Marshal.ReleaseComObject(xlRange) != 0) { }
*
* xlApp = null;
* xlWorkbook = null;
* xlRange = null;
*
* GC.Collect();
* GC.WaitForPendingFinalizers();
*
*
*/
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="sched"></param>
/// <param name="doc"></param>
/// <param name="pt"></param>
/// <param name="contentOnly"></param>
public void AddScheduleData(string filePath, ViewSchedule sched, Document doc, PathType pt, bool contentOnly)
{
string docPath;
if (doc.IsWorkshared)
{
docPath = ModelPathUtils.ConvertModelPathToUserVisiblePath(doc.GetWorksharingCentralModelPath());
}
else
{
docPath = doc.PathName;
}
string fullPath;
if (pt == PathType.Absolute)
{
fullPath = filePath;
}
else
{
fullPath = PathExchange.GetFullPath(filePath, docPath);
}
// Get the file path
excelFilePath = fullPath;
if (!File.Exists(excelFilePath))
{
return;
}
// read the Excel file and create the schedule
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);
Excel.Sheets worksheets = workbook.Worksheets;
worksheet = null;
foreach (Excel.Worksheet ws in worksheets)
{
if (ws.Name.Trim() == sched.Name.Trim())
{
worksheet = ws;
}
}
if (worksheet == null)
{
return;
}
//TaskDialog.Show("Test", "Worksheet found");
// Find the ThinLine linestyle
CategoryNameMap lineSubCats = doc.Settings.Categories.get_Item(BuiltInCategory.OST_Lines).SubCategories;
ElementId thinLineStyle = new ElementId(-1);
ElementId hairlineStyle = new ElementId(-1);
ElementId thinStyle = new ElementId(-1);
ElementId mediumStyle = new ElementId(-1);
ElementId thickStyle = new ElementId(-1);
foreach (Category style in lineSubCats)
{
if (style.Name == "Thin Lines")
{
thinLineStyle = style.Id;
}
if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.hairlineInt)
{
hairlineStyle = style.Id;
}
else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thinInt)
{
thinStyle = style.Id;
}
else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.mediumInt)
{
mediumStyle = style.Id;
}
else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thickInt)
{
thickStyle = style.Id;
}
}
if (hairlineStyle.IntegerValue == -1)
{
hairlineStyle = thinLineStyle;
}
if (thinStyle.IntegerValue == -1)
{
thinStyle = thinLineStyle;
}
if (mediumStyle.IntegerValue == -1)
{
mediumStyle = thinLineStyle;
}
if (thickStyle.IntegerValue == -1)
{
thickStyle = thinLineStyle;
}
// Find out how many rows and columns we need in the schedule
Excel.Range rng = ActualUsedRange(worksheet);
Excel.Range range = rng;
int rowCount = range.Rows.Count;
int columnCount = range.Columns.Count;
// Get the schedule body to set the overall width
TableSectionData bodyData = sched.GetTableData().GetSectionData(SectionType.Body);
if (!contentOnly)
{
double schedWidth = range.Columns.Width;
try
{
bodyData.SetColumnWidth(0, (schedWidth * pointWidthInches) / 12);
}
catch { }
}
// Get the header body to create the necessary rows and columns
TableSectionData headerData = sched.GetTableData().GetSectionData(SectionType.Header);
if (!contentOnly)
{
//TaskDialog.Show("Test: ", "Row Count: " + rowCount.ToString() + "\nColumn Count: " + columnCount.ToString());
for (int i = 0; i < columnCount - 1; i++)
{
headerData.InsertColumn(1);
}
for (int i = 0; i < rowCount - 1; i++)
{
headerData.InsertRow(1);
}
for (int i = 1; i <= headerData.NumberOfColumns; i++)
{
try
{
Excel.Range cell = worksheet.Cells[1, i];
headerData.SetColumnWidth(i - 1, (cell.Width * pointWidthInches) / 12);
}
catch { }
}
for (int i = 1; i <= headerData.NumberOfRows; i++)
{
try
{
Excel.Range cell = worksheet.Cells[i, 1];
headerData.SetRowHeight(i - 1, (cell.Height * pointWidthInches) / 12);
}
catch { }
}
}
List <TableMergedCell> mergedCells = new List <TableMergedCell>();
int errorCount = 0;
for (int i = 1; i <= headerData.NumberOfRows; i++) // Iterate through rows of worksheet data
{
for (int j = 1; j <= headerData.NumberOfColumns; j++) // Iterate through columns of worksheet data
{
// Get the current cell in the worksheet grid
Excel.Range cell = worksheet.Cells[i, j];
// If adjusting the formatting or adding content is not necessary,
// just update the text content. This is via a UI switch.
if (contentOnly)
{
try
{
headerData.SetCellText(i - 1, j - 1, cell.Text);
continue;
}
catch {
errorCount++;
continue;
}
}
Excel.Font font = cell.Font;
Excel.DisplayFormat dispFormat = cell.DisplayFormat;
TableCellStyle cellStyle = new TableCellStyle();
TableCellStyleOverrideOptions styleOverride = cellStyle.GetCellStyleOverrideOptions();
Excel.Border topEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeTop];
Excel.Border bottomEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeBottom];
Excel.Border leftEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeLeft];
Excel.Border rightEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeRight];
// Determine Bottom Edge Line Style
if (bottomEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderBottomLineStyle = new ElementId(-1);
}
else
{
switch (bottomEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderBottomLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderBottomLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderBottomLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderBottomLineStyle = thickStyle;
break;
}
}
// Determine Top Edge Line Style
if (topEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderTopLineStyle = new ElementId(-1);
}
else
{
switch (topEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderTopLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderTopLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderTopLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderTopLineStyle = thickStyle;
break;
}
}
// Determine Left Edge Line Style
if (leftEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderLeftLineStyle = new ElementId(-1);
}
else
{
switch (leftEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderLeftLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderLeftLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderLeftLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderLeftLineStyle = thickStyle;
break;
}
}
// Determine Right Edge Line Style
if (rightEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderRightLineStyle = new ElementId(-1);
}
else
{
switch (rightEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderRightLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderRightLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderRightLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderRightLineStyle = thickStyle;
break;
}
}
// Border Styles are always overridden
styleOverride.BorderBottomLineStyle = true;
styleOverride.BorderTopLineStyle = true;
styleOverride.BorderLeftLineStyle = true;
styleOverride.BorderRightLineStyle = true;
if (styleOverride.BorderBottomLineStyle || styleOverride.BorderTopLineStyle ||
styleOverride.BorderLeftLineStyle || styleOverride.BorderRightLineStyle)
{
styleOverride.BorderLineStyle = true;
}
// Get Background color and font name
System.Drawing.Color backGroundColor = System.Drawing.ColorTranslator.FromOle((int)cell.Interior.Color);
cellStyle.BackgroundColor = new Color(backGroundColor.R, backGroundColor.G, backGroundColor.B);
styleOverride.BackgroundColor = true;
cellStyle.FontName = cell.Font.Name;
styleOverride.Font = true;
// Determine Horizontal Alignment
// If its not set to left, right or center, do not modify
switch (dispFormat.HorizontalAlignment)
{
case (int)Excel.XlHAlign.xlHAlignLeft:
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left;
styleOverride.HorizontalAlignment = true;
break;
case (int)Excel.XlHAlign.xlHAlignRight:
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right;
styleOverride.HorizontalAlignment = true;
break;
case (int)Excel.XlHAlign.xlHAlignGeneral: // No specific style assigned
// Check if it's a number which is typically right aligned
if (double.TryParse(cell.Text, out double alignTest))
{
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right;
styleOverride.HorizontalAlignment = true;
}
else // Assume text and left align it
{
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left;
styleOverride.HorizontalAlignment = true;
}
break;
case (int)Excel.XlHAlign.xlHAlignCenter:
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Center;
styleOverride.HorizontalAlignment = true;
break;
}
// Get the vertical alignment of the cell
switch (dispFormat.VerticalAlignment)
{
case (int)Excel.XlVAlign.xlVAlignBottom:
cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Bottom;
styleOverride.VerticalAlignment = true;
break;
case (int)Excel.XlVAlign.xlVAlignTop:
cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Top;
styleOverride.VerticalAlignment = true;
break;
default:
cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Middle;
styleOverride.VerticalAlignment = true;
break;
}
switch (dispFormat.Orientation)
{
case (int)Excel.XlOrientation.xlUpward:
cellStyle.TextOrientation = 9;
styleOverride.TextOrientation = true;
break;
case (int)Excel.XlOrientation.xlDownward:
cellStyle.TextOrientation = -9;
styleOverride.TextOrientation = true;
break;
case (int)Excel.XlOrientation.xlVertical:
cellStyle.TextOrientation = 9;
styleOverride.TextOrientation = true;
break;
default:
int rotation = (int)cell.Orientation;
if (rotation != (int)Excel.XlOrientation.xlHorizontal)
{
cellStyle.TextOrientation = rotation;
styleOverride.TextOrientation = true;
}
break;
}
// Determine Text Size
double textSize = Convert.ToDouble(font.Size);
//double newTextSize = (textSize / 72) / 12;
cellStyle.TextSize = textSize;
styleOverride.FontSize = true;
// Determine Font Color
System.Drawing.Color fontColor = System.Drawing.ColorTranslator.FromOle((int)font.Color);
cellStyle.TextColor = new Color(fontColor.R, fontColor.G, fontColor.B);
styleOverride.FontColor = true;
// NOTES: Bold is a bool
// Italic is a bool
// Underline is an int
cellStyle.IsFontBold = (bool)font.Bold;
cellStyle.IsFontItalic = (bool)font.Italic;
cellStyle.IsFontUnderline = (int)font.Underline == 2;
styleOverride.Bold = true;
styleOverride.Italics = true;
styleOverride.Underline = true;
cellStyle.SetCellStyleOverrideOptions(styleOverride);
if (cell.MergeCells == true)
{
TableMergedCell tmc = new TableMergedCell()
{
Left = j - 1,
Right = cell.MergeArea.Columns.Count - 1,
Top = i - 1,
Bottom = (i - 1) + cell.MergeArea.Rows.Count - 1
};
// Check to see if the cell is already merged...
bool alreadyMerged = false;
foreach (TableMergedCell mergedCell in mergedCells)
{
bool left = false;
bool right = false;
bool top = false;
bool bottom = false;
if (i - 1 >= mergedCell.Top)
{
top = true;
}
if (i - 1 <= mergedCell.Bottom)
{
bottom = true;
}
if (j - 1 >= mergedCell.Left)
{
left = true;
}
if (j - 1 <= mergedCell.Right)
{
right = true;
}
//TaskDialog.Show("MergedCell", string.Format("Top: {0}\nBottom: {1}\nLeft: {2}\nRight: {3}\ni-1: {4}\nj-1: {5}", mergedCell.Top, mergedCell.Bottom, mergedCell.Left, mergedCell.Right, i - 1, j - 1));
if (top && bottom && left && right)
{
alreadyMerged = true;
break;
}
}
if (!alreadyMerged)
{
try
{
headerData.MergeCells(tmc);
headerData.SetCellText(i - 1, j - 1, cell.Text);
headerData.SetCellStyle(i - 1, j - 1, cellStyle);
j += cell.MergeArea.Columns.Count - 1;
mergedCells.Add(tmc);
// TaskDialog.Show("Test", string.Format("This cell [{0},{1}] is merged.\nMerged Area: [{2},{3}]", cell.Row - 1, cell.Column - 1, cell.MergeArea.Rows.Count.ToString(), cell.MergeArea.Columns.Count.ToString()));
}
catch
{
}
}
}
else
{
//TaskDialog.Show("Non Merged", string.Format("This cell is not merged with any others [{0}, {1}]", i - 1, j - 1));
try
{
headerData.SetCellText(i - 1, j - 1, cell.Text);
headerData.SetCellStyle(i - 1, j - 1, cellStyle);
}
catch { }
}
}
}
if (errorCount > 0)
{
TaskDialog.Show("Warning", "Error reloading content for " + errorCount.ToString() + " cells.\n\nConsider unchecking the \"Content Only\" checkbox and reloading the schedule to force it to rebuild.");
}
// Write the Schema to the project
Schema schema = null;
try
{
schema = Schema.Lookup(schemaGUID);
}
catch { }
ModifySchemaData(schema, sched.Id);
workbook.Close(false);
Marshal.ReleaseComObject(worksheets);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
}
//Возвращает Коллекцию записей из БД
public ArrayList GetData(bool rez)
{
ArrayList listZapisey = new ArrayList();
int count = 1;
string FileName = LocalBase;
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;
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;
int id = 0;
string name = null;
string description = null;
string sourse = null;
string target = null;
bool privacy = true;
bool integrity = true;
bool availability = true;
if (rez)
{
Zapis zapis;
for (int i = 3; i <= RowsCount; i++)
{
for (int j = 1; j <= ColumnsCount; j++)
{
Excel.Range CellRange = UsedRange.Cells[i, j];
// Получение текста ячейки
string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
(CellRange as Excel.Range).Value2.ToString();
if (count == 1)
{
id = Convert.ToInt32(CellText);
count++;
}
else if (count == 2)
{
name = CellText;
count++;
}
else if (count == 3)
{
description = CellText;
count++;
}
else if (count == 4)
{
sourse = CellText;
count++;
}
else if (count == 5)
{
target = CellText;
count++;
}
else if (count == 6)
{
if (CellText == "0")
{
privacy = false;
}
else
{
privacy = true;
}
count++;
}
else if (count == 7)
{
if (CellText == "0")
{
integrity = false;
}
else
{
integrity = true;
}
count++;
}
else if (count == 8)
{
if (CellText == "0")
{
availability = false;
}
else
{
availability = true;
}
count++;
}
else if (count == 9)
{
count++;
}
else if (count == 10)
{
zapis = new Zapis {
Id = id, Name = name, Description = description, Sourse = sourse, Target = target, Privacy = privacy, Integrity = integrity, Availability = availability
};
listZapisey.Add(zapis);
count = 1;;
id = 0;
name = null;
description = null;
sourse = null;
target = null;
privacy = true;
integrity = true;
availability = true;
}
}
}
}
else
{
ZapisMin zapis;
for (int i = 3; i <= RowsCount; i++)
{
for (int j = 1; j <= ColumnsCount; j++)
{
Excel.Range CellRange = UsedRange.Cells[i, j];
// Получение текста ячейки
string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
(CellRange as Excel.Range).Value2.ToString();
if (count == 1)
{
description = "УБИ." + Convert.ToInt32(CellText);
count++;
}
else if (count == 2)
{
name = CellText;
count++;
}
else if (count == 3)
{
count++;
}
else if (count == 4)
{
count++;
}
else if (count == 5)
{
count++;
}
else if (count == 6)
{
count++;
}
else if (count == 7)
{
count++;
}
else if (count == 8)
{
count++;
}
else if (count == 9)
{
count++;
}
else if (count == 10)
{
zapis = new ZapisMin {
Id = description, Name = name
};
listZapisey.Add(zapis);
count = 1;;
id = 0;
name = null;
description = null;
sourse = null;
target = null;
privacy = true;
integrity = true;
availability = true;
}
}
}
}
}
return(listZapisey);
}
// Create a new schedule
public ViewSchedule CreateSchedule(string filePath, UIDocument uidoc)
{
ViewSchedule sched = null;
_doc = uidoc.Document;
if (uidoc.Document.IsWorkshared)
{
docPath = ModelPathUtils.ConvertModelPathToUserVisiblePath(uidoc.Document.GetWorksharingCentralModelPath());
}
else
{
docPath = uidoc.Document.PathName;
}
excelFilePath = filePath;
if (File.Exists(excelFilePath))
{
// read the Excel file and create the schedule
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath, ReadOnly: true);
Excel.Sheets worksheets = workbook.Worksheets;
List <WorksheetObject> worksheetObjs = new List <WorksheetObject>();
foreach (Excel.Worksheet ws in worksheets)
{
WorksheetObject wo = new WorksheetObject();
string name = ws.Name;
wo.Name = name;
Excel.Range range = ws.UsedRange;
try
{
range.CopyPicture(Excel.XlPictureAppearance.xlPrinter, Excel.XlCopyPictureFormat.xlBitmap);
if (Clipboard.GetDataObject() != null)
{
IDataObject data = Clipboard.GetDataObject();
if (data.GetDataPresent(DataFormats.Bitmap))
{
System.Drawing.Image img = (System.Drawing.Image)data.GetData(DataFormats.Bitmap, true);
wo.Image = img;
}
}
}
catch { }
worksheetObjs.Add(wo);
}
// Pop up the worksheet form
WorksheetSelectForm wsForm = new WorksheetSelectForm(worksheetObjs, this, _doc);
// Revit version
int version = Convert.ToInt32(uidoc.Application.Application.VersionNumber);
// Get the Revit window handle
IntPtr handle = IntPtr.Zero;
if (version < 2019)
{
handle = System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle;
}
else
{
handle = uidoc.Application.GetType().GetProperty("MainWindowHandle") != null
? (IntPtr)uidoc.Application.GetType().GetProperty("MainWindowHandle").GetValue(uidoc.Application)
: IntPtr.Zero;
}
System.Windows.Interop.WindowInteropHelper wih = new System.Windows.Interop.WindowInteropHelper(wsForm)
{
Owner = handle
};
//Show the Worksheet Select form
wsForm.ShowDialog();
if (wsForm.DialogResult.HasValue && wsForm.DialogResult.Value)
{
foreach (Excel.Worksheet ws in worksheets)
{
if (ws.Name == selectedWorksheet.Name)
{
worksheet = ws;
break;
}
}
}
else
{
worksheet = null;
}
if (worksheet != null)
{
workSheetName = worksheet.Name;
Transaction trans = new Transaction(_doc, "Create Schedule");
trans.Start();
// Create the schedule
sched = ViewSchedule.CreateSchedule(_doc, new ElementId(-1));
sched.Name = worksheet.Name;
// Add a single parameter for data, Assembly Code
ElementId assemblyCodeId = new ElementId(BuiltInParameter.UNIFORMAT_DESCRIPTION);
ScheduleFieldId fieldId = null;
foreach (SchedulableField sField in sched.Definition.GetSchedulableFields())
{
ElementId paramId = sField.ParameterId;
if (paramId == assemblyCodeId)
{
ScheduleField field = sched.Definition.AddField(sField);
fieldId = field.FieldId;
break;
}
}
if (fieldId != null && sched.Definition.GetFieldCount() > 0)
{
ScheduleDefinition schedDef = sched.Definition;
// Add filters to hide all elements in the schedule, ie make sure nothing shows up in the body.
ScheduleFilter filter0 = new ScheduleFilter(fieldId, ScheduleFilterType.Equal, "NO VALUES FOUND");
ScheduleFilter filter1 = new ScheduleFilter(fieldId, ScheduleFilterType.Equal, "ALL VALUES FOUND");
schedDef.AddFilter(filter0);
schedDef.AddFilter(filter1);
// Turn off the headers
schedDef.ShowHeaders = false;
// Fill out the schedule from Excel data
AddScheduleData(filePath, sched, _doc, PathType.Absolute, false);
}
if (linkFile)
{
AssignSchemaData(sched.Id, workSheetName, _doc);
}
trans.Commit();
}
//workbook.Close();
workbook.Close(false);
Marshal.ReleaseComObject(worksheets);
if (worksheet != null)
{
Marshal.ReleaseComObject(worksheet);
}
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
}
return(sched);
}
/// <summary>
/// 英文版
/// </summary>
/// <param name="AppDT"></param>
/// <param name="DetailDT"></param>
/// <returns></returns>
private bool CreateEnXLS(DataTable AppDT, DataTable DetailDT)
{
string XLSName;
decimal MoneyDiscont = 1;
decimal MaxMoney = 0;
string strMoneyUnit = "";
double douTotalPrice = 0.00;
switch (AppDT.Rows[0]["MoneyUnit"].ToString())
{
case "1":
default:
MoneyDiscont = 1;
break;
case "2":
MoneyDiscont = EmailControl.config.USrate;
MaxMoney = EmailControl.config.USD;
strMoneyUnit = "US";
break;
case "3":
MoneyDiscont = EmailControl.config.HKrate;
MaxMoney = EmailControl.config.HKD;
strMoneyUnit = "HKD";
break;
case "4":
MoneyDiscont = EmailControl.config.MOPrate;
MaxMoney = EmailControl.config.MOP;
strMoneyUnit = "MOP";
break;
case "5":
MoneyDiscont = EmailControl.config.SGDrate;
MaxMoney = EmailControl.config.SGD;
strMoneyUnit = "SGD";
break;
case "6":
MoneyDiscont = EmailControl.config.MYRrate;
MaxMoney = EmailControl.config.MYR;
strMoneyUnit = "MYR";
break;
case "7":
MoneyDiscont = EmailControl.config.GBPrate;
MaxMoney = EmailControl.config.GBP;
strMoneyUnit = "GBP";
break;
case "8":
MoneyDiscont = EmailControl.config.EURrate;
MaxMoney = EmailControl.config.EUR;
strMoneyUnit = "EUR";
break;
case "9":
MoneyDiscont = EmailControl.config.JPYrate;
MaxMoney = EmailControl.config.JPY;
strMoneyUnit = "JPY";
break;
case "10":
MoneyDiscont = EmailControl.config.TWDrate;
MaxMoney = EmailControl.config.TWD;
strMoneyUnit = "TWD";
break;
}
string strSalesDate;
if (AppDT.Rows[0]["ApplicantsDate"].ToString() != null && AppDT.Rows[0]["ApplicantsDate"].ToString() != "")
{
strSalesDate = DateTime.Parse(AppDT.Rows[0]["SalesDate"].ToString()).ToShortDateString();
}
else
{
strSalesDate = DateTime.Now.ToShortDateString();
}
XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\2015 Staff Purchase Form-in HK -templet .xls";
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbooks wbks = app.Workbooks;
Excel._Workbook _wbk = wbks.Add(XLSName);
Excel.Sheets shs = _wbk.Sheets;
Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);
//写入
_wsh.Cells[4, 3] = AppDT.Rows[0]["ApplicantsName"].ToString();
_wsh.Cells[4, 11] = AppDT.Rows[0]["ApplicantsNo"].ToString();
_wsh.Cells[5, 3] = AppDT.Rows[0]["Location"].ToString();
_wsh.Cells[5, 11] = AppDT.Rows[0]["PurchaseLocation"].ToString();
_wsh.Cells[22, 2] = "For gift purchases of items with unit RSP exceeding " + strMoneyUnit + MaxMoney + ", please complete the following :";
_wsh.Cells[22, 11] = " Total " + strMoneyUnit + ":";
//_wsh.Cells[22, 12] = AppDT.Rows[0]["TotalPrice"].ToString();
_wsh.Cells[24, 12] = AppDT.Rows[0]["TransNo"].ToString();
//_wsh.Cells[23, 12] = strSalesDate;
_wsh.Cells[31, 5] = AppDT.Rows[0]["ApplicantsName"].ToString();
if (AppDT.Rows[0]["ApplicantsDate"].ToString() != null && AppDT.Rows[0]["ApplicantsDate"].ToString() != "" && AppDT.Rows[0]["ApplicantsName"].ToString() != "")
{
_wsh.Cells[31, 7] = DateTime.Parse(AppDT.Rows[0]["ApplicantsDate"].ToString()).ToShortDateString();
}
_wsh.Cells[32, 5] = AppDT.Rows[0]["ApprovalName2"].ToString();
if (AppDT.Rows[0]["ApprovalDate2"].ToString() != null && AppDT.Rows[0]["ApprovalDate2"].ToString() != "" && AppDT.Rows[0]["ApprovalName2"].ToString() != "")
{
_wsh.Cells[32, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate2"].ToString()).ToShortDateString();
}
_wsh.Cells[33, 5] = AppDT.Rows[0]["ApprovalName"].ToString();
if (AppDT.Rows[0]["ApprovalDate"].ToString() != null && AppDT.Rows[0]["ApprovalDate"].ToString() != "" && AppDT.Rows[0]["ApprovalName"].ToString() != "")
{
_wsh.Cells[33, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate"].ToString()).ToShortDateString();
}
_wsh.Cells[34, 5] = AppDT.Rows[0]["ApprovalName3"].ToString();
if (AppDT.Rows[0]["ApprovalDate3"].ToString() != null && AppDT.Rows[0]["ApprovalDate3"].ToString() != "" && AppDT.Rows[0]["ApprovalName3"].ToString() != "")
{
_wsh.Cells[34, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate3"].ToString()).ToShortDateString();
}
_wsh.Cells[35, 5] = AppDT.Rows[0]["StaffName"].ToString();
_wsh.Cells[36, 2] = "Approved by HK Finance Manager:\r\n (If unit RSP exceeding " + strMoneyUnit + MaxMoney + ")";
if (AppDT.Rows[0]["StaffName"].ToString() != "")
{
_wsh.Cells[35, 7] = strSalesDate;
_wsh.Cells[23, 12] = strSalesDate;
}
else
{
_wsh.Cells[35, 7] = "";
_wsh.Cells[23, 12] = "";
}
int j = 0;
int i = 0;
foreach (DataRow dr in DetailDT.Rows)
{
if (i < 6)
{
_wsh.Cells[8 + 2 * i, 2] = dr["CodeID"].ToString();
_wsh.Cells[8 + 2 * i, 4] = dr["ItemID"].ToString();
_wsh.Cells[8 + 2 * i, 5] = dr["Detail"].ToString();
_wsh.Cells[8 + 2 * i, 7] = dr["Count"].ToString();
_wsh.Cells[8 + 2 * i, 8] = dr["Price"].ToString();
if (dr["SelforGift"].ToString() == "2")
{
_wsh.Cells[8 + 2 * i, 9] = "Gift";
}
else
{
_wsh.Cells[8 + 2 * i, 9] = "Self";
}
_wsh.Cells[8 + 2 * i, 10] = dr["ApprovalCount"].ToString();
_wsh.Cells[8 + 2 * i, 11] = dr["ApprovalDiscount"].ToString();
_wsh.Cells[8 + 2 * i, 12] = dr["FinalPrice"].ToString();
douTotalPrice = douTotalPrice + Convert.ToDouble(dr["FinalPrice"].ToString());
i++;
}
if (j < 3)
{
if (dr["SelforGift"].ToString() == "2")
{
_wsh.Cells[24 + j, 2] = dr["CodeID"].ToString();
_wsh.Cells[24 + j, 3] = dr["Recipient"].ToString();
_wsh.Cells[24 + j, 5] = dr["Relationship"].ToString();
_wsh.Cells[24 + j, 6] = dr["Reason"].ToString();
j++;
}
}
}
_wsh.Cells[22, 12] = douTotalPrice;
//保存
string filePath = System.IO.Directory.GetCurrentDirectory() + @"\tempPDF\tempExcel.xls";
app.AlertBeforeOverwriting = false;
_wbk.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//退出和释放
_wbk.Close(null, null, null);
wbks.Close();
app.Quit();
//释放掉多余的excel进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
return(true);
}
static void Main(string[] args)
{
Excel.Application app = null;
Excel.Workbooks books = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheet = null;
string arquivo = @"\\D5668m001e035\operacoes\Andre\201610_RELATORIO_ANALÍTICO_VENDAS__CREDSYSTEM_251020160123.xlsx";
string arquivoCarga = Path.Combine(@"\\D5668m001e035\OPERACOES\Andre\Carga\", Path.GetFileName(arquivo));
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + arquivoCarga + "; Extended Properties='Excel 12.0; HDR=NO'";
string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbTeste_Carga;Data Source=D5668M001E035;Connect Timeout=0";
// Copia o arquivo para o diretório de carga para preservar o original:
File.Copy(arquivo, arquivoCarga, true);
try
{
app = new Excel.Application();
books = app.Workbooks;
book = app.Workbooks.Open(arquivoCarga, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
List <string> lista = new List <string>();
// Pega os nomes das planilhas:
foreach (Excel.Worksheet plan in book.Worksheets)
{
lista.Add((string)plan.Name);
}
// Para cada planilha que não seja de TOTAIS:
foreach (string nome in lista)
{
if (!nome.Contains("TOTAIS"))
{
sheet = book.Worksheets.get_Item(nome);
// Deleta as 7 primeiras linhas que não fazem parte do processo:
Excel.Range range = sheet.get_Range("A1", "A7".ToString());
range.EntireRow.Delete(Excel.XlDirection.xlUp);
book.Save();
using (OleDbConnection oleDbConnection = new OleDbConnection(excelConnectionString))
{
oleDbConnection.Open();
using (DataTable dataTable = new DataTable())
{
using (OleDbCommand oleDbCommand = new OleDbCommand("SELECT * FROM [" + nome + "$]", oleDbConnection))
{
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand))
{
oleDbDataAdapter.Fill(dataTable);
}
}
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnectionString))
{
sqlBulkCopy.DestinationTableName = "tbBS_tmp_CredSystem";
sqlBulkCopy.ColumnMappings.Add(0, "Semana");
sqlBulkCopy.ColumnMappings.Add(1, "Data Inc Documento");
sqlBulkCopy.ColumnMappings.Add(2, "Nome da Loja");
sqlBulkCopy.ColumnMappings.Add(3, "Número Produto");
sqlBulkCopy.ColumnMappings.Add(4, "Nome Campanha");
sqlBulkCopy.ColumnMappings.Add(5, "Número de Parcelas");
sqlBulkCopy.ColumnMappings.Add(6, "Data Emissão");
sqlBulkCopy.ColumnMappings.Add(7, "Dia do Processamento");
sqlBulkCopy.ColumnMappings.Add(8, "Número Apólice");
sqlBulkCopy.ColumnMappings.Add(9, "Número Certificado");
sqlBulkCopy.ColumnMappings.Add(10, "Prêmio Documento");
sqlBulkCopy.ColumnMappings.Add(11, "Número Cliente");
sqlBulkCopy.ColumnMappings.Add(12, "Chave Segurado").ToString(); // Se o campo for numérico e estiver perdendo formatação, utilizar o ToString()
sqlBulkCopy.ColumnMappings.Add(13, "Nome Segurado");
sqlBulkCopy.ColumnMappings.Add(14, "Número Campanha");
sqlBulkCopy.ColumnMappings.Add(15, "Início de Vigência");
sqlBulkCopy.ColumnMappings.Add(16, "Data Liberação");
sqlBulkCopy.ColumnMappings.Add(17, "Fim Vigência");
sqlBulkCopy.ColumnMappings.Add(18, "Número Forma Parcelamento");
sqlBulkCopy.ColumnMappings.Add(19, "Número Ciclo Adm Cobrança");
sqlBulkCopy.WriteToServer(dataTable);
}
}
}
}
}
book.Close(true, Type.Missing, Type.Missing);
app.Quit();
// Deleta o arquivo modificado ao final da carga:
File.Delete(arquivoCarga);
}
catch (Exception e)
{
throw e;
}
finally
{
KillProcessoExcelEspecifico(arquivoCarga);
//Console.ReadKey();
}
}
private bool CreateXLS(DataTable AppDT, DataTable DetailDT, string title)
{
double douTotalPrice = 0.00;
string XLSName;
XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\2016国内员购申请表-模板.xls";
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbooks wbks = app.Workbooks;
Excel._Workbook _wbk = wbks.Add(XLSName);
Excel.Sheets shs = _wbk.Sheets;
Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);
//写入
_wsh.Cells[2, 2] = title;
_wsh.Cells[4, 3] = AppDT.Rows[0]["ApplicantsName"].ToString();
_wsh.Cells[4, 10] = AppDT.Rows[0]["ApplicantsNo"].ToString();
_wsh.Cells[5, 3] = AppDT.Rows[0]["Location"].ToString();
_wsh.Cells[5, 10] = AppDT.Rows[0]["PurchaseLocation"].ToString();
_wsh.Cells[22, 11] = AppDT.Rows[0]["TotalPrice"].ToString();
_wsh.Cells[24, 11] = AppDT.Rows[0]["TransNo"].ToString();
_wsh.Cells[23, 11] = DateTime.Now.ToShortDateString();
_wsh.Cells[31, 4] = AppDT.Rows[0]["ApplicantsName"].ToString();
_wsh.Cells[31, 6] = DateTime.Parse(AppDT.Rows[0]["ApplicantsDate"].ToString()).ToShortDateString();
_wsh.Cells[32, 4] = AppDT.Rows[0]["ApprovalName2"].ToString();
_wsh.Cells[32, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate2"].ToString()).ToShortDateString();
_wsh.Cells[33, 4] = AppDT.Rows[0]["ApprovalName"].ToString();
_wsh.Cells[33, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate"].ToString()).ToShortDateString();
_wsh.Cells[34, 4] = AppDT.Rows[0]["ApprovalName3"].ToString();
if (AppDT.Rows[0]["ApprovalName3"].ToString() != "")
{
_wsh.Cells[34, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate3"].ToString()).ToShortDateString();
}
else
{
_wsh.Cells[34, 6] = "";
}
int j = 0;
int i = 0;
foreach (DataRow dr in DetailDT.Rows)
{
if (i < 6)
{
_wsh.Cells[9 + 2 * i, 2] = dr["CodeID"].ToString();
_wsh.Cells[9 + 2 * i, 3] = dr["ItemID"].ToString();
_wsh.Cells[9 + 2 * i, 4] = dr["Detail"].ToString();
_wsh.Cells[9 + 2 * i, 6] = dr["Count"].ToString();
_wsh.Cells[9 + 2 * i, 7] = dr["Price"].ToString();
if (dr["SelforGift"].ToString() == "2")
{
_wsh.Cells[9 + 2 * i, 8] = "送礼";
}
else
{
_wsh.Cells[9 + 2 * i, 8] = "自用";
}
_wsh.Cells[9 + 2 * i, 9] = dr["ApprovalCount"].ToString();
_wsh.Cells[9 + 2 * i, 10] = dr["ApprovalDiscount"].ToString();
_wsh.Cells[9 + 2 * i, 11] = dr["FinalPrice"].ToString();
i++;
douTotalPrice += double.Parse(dr["FinalPrice"].ToString());
}
if (j < 3)
{
if (dr["SelforGift"].ToString() == "2")
{
_wsh.Cells[24 + j, 2] = dr["CodeID"].ToString();
_wsh.Cells[24 + j, 3] = dr["Recipient"].ToString();
_wsh.Cells[24 + j, 4] = dr["Relationship"].ToString();
_wsh.Cells[24 + j, 5] = dr["Reason"].ToString();
j++;
}
}
}
_wsh.Cells[22, 11] = douTotalPrice.ToString();
//保存
string filePath = System.IO.Directory.GetCurrentDirectory() + @"\tempPDF\tempExcel.xls";
app.AlertBeforeOverwriting = false;
_wbk.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//退出和释放
_wbk.Close(null, null, null);
wbks.Close();
app.Quit();
//释放掉多余的excel进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
return(true);
}
public static void GravaRetorno()
{
#region Salva a lista de retorno no Excel
Excel.Application xlsApp = new Excel.Application();
Excel.Workbook xlsWorkbook = xlsApp.Workbooks.Open(Form1.path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "", false, false, 0, false, false, false);
Excel.Worksheet newWorksheetErro;
Excel.Worksheet newWorksheetOk;
try
{
//Add a worksheet to the workbook.
newWorksheetErro = xlsApp.Worksheets.Add();
newWorksheetOk = xlsApp.Worksheets.Add();
newWorksheetErro.Name = "WebServiceVipp - Erros";
//Name the sheet.
newWorksheetOk.Name = "WebServiceVipp - ok";
//Get the Cells collection.
Excel.Sheets xlsSheets = xlsWorkbook.Worksheets;
//For que acessa todas as planilhas
foreach (Excel.Worksheet xlsWorksheet in xlsSheets)
{
//Acessa a aba da Planilha com o nome "WebServiceVipp"
if (xlsWorksheet.Name.Trim().Equals("WebServiceVipp - ok"))
{
Excel.Range xlsWorksRows = xlsWorksheet.Cells;
int cont = 0;
foreach (RetornoValida list in Retorno.lRetornoValida)
{
cont++;
xlsWorksRows.Item[cont, 1] = list.Observacao;
xlsWorksRows.Item[cont, 2] = list.Nome;
xlsWorksRows.Item[cont, 3] = list.Status;
xlsWorksRows.Item[cont, 4] = list.Etiqueta;
}
}
if (xlsWorksheet.Name.Trim().Equals("WebServiceVipp - Erros"))
{
Excel.Range xlsWorksRowss = xlsWorksheet.Cells;
int cont = 0;
foreach (RetornoInvalida list in Retorno.lRetornoInvalida)
{
cont++;
if (!list.Observacao.Equals(string.Empty) || !list.Observacao.Equals(null))
{
xlsWorksRowss.Item[cont, 1] = list.Observacao;
xlsWorksRowss.Item[cont, 2] = list.Nome;
xlsWorksRowss.Item[cont, 3] = list.Status;
xlsWorksRowss.Item[cont, 4] = list.Erro;
}
}
}
}
}
catch (System.Runtime.InteropServices.COMException)
{
MessageBox.Show("Não foi possivel gravar o retorno no arquivo processado, verifique se a planilha está bloqueada", "Erro", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
DateTime saveNow = DateTime.Now;
string sdf = saveNow.ToString("dd-MM-yyyy_hh-mm");
string nomeArquivo = Form1.caminhoArquivo + "\\" + Form1.nomeArquivo + " " + sdf + ".xlsx";
xlsApp.ActiveWorkbook.SaveAs(nomeArquivo);
xlsApp.Quit();
#endregion
}
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 HashSet <CellUpdate> GetAllCells()
{
Excel.Worksheet currSheet = null;
Excel.Sheets sheets = null;
Excel.Workbook activeWb = null;
HashSet <CellUpdate> myCells = new HashSet <CellUpdate>();
//System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
//stopwatch.Start();
try
{
activeWb = GlobalFunctions.FindActiveWorkbook();
sheets = activeWb.Worksheets;
foreach (Excel.Worksheet ws in sheets)
{
currSheet = ws;
String wsName = currSheet.Name.ToUpper();
Int32 LastCol;
Int32 LastRow;
WorksheetExt wsBoundary;
if (GlobalFunctions.worksheetBounds.Keys.Contains(wsName))
{
wsBoundary = GlobalFunctions.worksheetBounds[wsName];
}
else
{
wsBoundary = new WorksheetExt(wsName);
GlobalFunctions.worksheetBounds.Add(wsName, wsBoundary);
}
LastCol = wsBoundary.LastCol;
LastRow = wsBoundary.LastRow;
Excel.Range range = ws.Range["A1", GlobalFunctions.GetExcelColumnName(LastCol) + LastRow];
// FOR LOOP ON VALUES
dynamic formulaArrayD = range.Formula;
GlobalFunctions.WaitForApplicationReady();
Object[,] formulaArray = formulaArrayD as Object[, ];
String[,] commentArray = GetComments(wsName, range, LastRow, LastCol);
if (formulaArray != null)
{
for (int i = 1; i <= LastRow; i++)
{
for (int j = 1; j <= LastCol; j++)
{
if (formulaArray[i, j] != null)
{
if (formulaArray[i, j].ToString() != "")
{
CellUpdate uc = new CellUpdate(i, j, wsName, formulaArray[i, j].ToString(), Enums.CellChangeType.Value, "", DateTime.MinValue);
myCells.Add(uc);
}
}
}
}
}
if (commentArray != null)
{
for (int i = 1; i <= LastRow; i++)
{
for (int j = 1; j <= LastCol; j++)
{
if (commentArray[i, j] != null)
{
if (commentArray[i, j] != "")
{
CellUpdate uc = new CellUpdate(i, j, wsName, commentArray[i, j].ToString(), Enums.CellChangeType.Comment, "", DateTime.MinValue);
myCells.Add(uc);
}
}
}
}
}
if (range != null)
{
Marshal.ReleaseComObject(range);
}
if (ws != null)
{
Marshal.ReleaseComObject(ws);
}
}
return(myCells);
}
catch (Exception ex)
{
if (ex.Message == "Incorrect ActiveWB Name")
{
Globals.ThisAddIn.DisableSync();
}
throw;
}
finally
{
if (currSheet != null)
{
Marshal.ReleaseComObject(currSheet);
}
if (sheets != null)
{
Marshal.ReleaseComObject(sheets);
}
if (activeWb != null)
{
Marshal.ReleaseComObject(activeWb);
}
//GlobalFunctions.InfoLog(String.Format("GetAllCells: {0}", stopwatch.Elapsed));
}
}
private void createReportButton_Click(object sender, EventArgs e)
{
if (Directory.Exists(Settings.Default.FilesDir))
{
createReportButton.Enabled = false;
// Получаем список файлов в каталоге
string[] filesArray = Directory.GetFiles(Settings.Default.FilesDir);
foreach (string fileName in filesArray)
{
// Проверяем расширение файла
FileInfo infoFile = new FileInfo(fileName);
// Совпадает с датами формата: 1900-01-01 2007/08/13 1900.01.01 1900 01 01 1900-01.01
//string fileNamePattern = @"(19|20)\d\d([- /.])(0[1-9]|1[012])([- /.])(0[1-9]|[12][0-9]|3[01])";
string month;
if (dateTimePicker1.Value.Month <= 9) { month = "0" + dateTimePicker1.Value.Month; }
else { month = dateTimePicker1.Value.Month.ToString(); };
string fileNamePattern;
if (checkBox1.Checked)
{
fileNamePattern = "(" + dateTimePicker1.Value.Year + @")([- /.])("
+ month + @")([- /.])(0[1-9]|[12][0-9]|3[01])";
}
else
{
fileNamePattern = ".";
}
// Обнуляем счётчик обработаных файлов
filesCount = 0;
if ((File.Exists(fileName)) && Regex.IsMatch(fileName, fileNamePattern) && (infoFile.Extension == ".dpm"))
{
filesCount++;
// Создаём потоки чтения файлов.
FileStream fileStream = new FileStream(
fileName,
FileMode.Open,
FileAccess.Read
);
StreamReader streamReader = new StreamReader(
fileStream,
Encoding.GetEncoding(1251)
);
toolStripStatusLabel2.Text = "Обработка файла: " + infoFile.Name;
// Считываем построчно файл до самого конца
while (!streamReader.EndOfStream)
{
// Разделяем полученную строку
// Структтура массива:
// info[0] - знак выхождения или выхода трека (">" или "<")
// info[1] - дата и время выхода пести
// info[2] - идентификатор трека при составлении плейлиста
// info[3] - псевдоним, который определяет положение треков на диске
// info[4] - имя файла трека относительно псевдонима
// info[5] - длительность трека
string[] info = streamReader.ReadLine().Split('\t');
// Считаем только вхождение файла
// Т.е. проверяем на символ ">" и проверяем только определённый псевдоним
string[] aliases = Properties.Settings.Default.Aliases.Split(';');
foreach (string alias in aliases)
{
if ((info[0].Trim() == ">") && (info[3].Trim() == alias.Trim()))
{
if (!tableCount.Contains(info[4]))
{
// Если записи нет, создаём новую
tableCount.Add(info[4], 1);
tableDuration.Add(info[4], info[5]);
}
else
{
// Если запись есть, увеличиваем счётчик
tableCount[info[4]] = (int)tableCount[info[4]] + 1;
}
}
}
}
// Закрываем потоки чтения файлов.
streamReader.Close();
fileStream.Close();
} // End If File Exist
} // End Foreach FileNames
// Меняем статус
toolStripStatusLabel2.Text = "Обработано файлов: " + filesCount;
// Создаём нумератор для управление Хэш массивом
IDictionaryEnumerator tableCountEnum = tableCount.GetEnumerator();
toolStripStatusLabel2.Text = "Создание файла отчёта.";
#region СОЗДАЁМ НОВЫЙ ДОКУМЕНТ EXCEL
// Открываем приложение
excelapp = new Excel.Application();
// Прячем окно программы
excelapp.Visible = false;
// Создаём документ с одной книгой
excelapp.SheetsInNewWorkbook = 1;
// Добавляем книгу
excelapp.Workbooks.Add(Type.Missing);
//Запрашивать сохранение
//excelapp.DisplayAlerts = true;
excelapp.DisplayAlerts = false;
//Получаем набор ссылок на объекты Workbook (на созданные книги)
excelappworkbooks = excelapp.Workbooks;
//Получаем ссылку на книгу 1 - нумерация от 1
excelappworkbook = excelappworkbooks[1];
#endregion
toolStripStatusLabel2.Text = "Генерация отчёта.";
#region РАБОТА С ЯЧЕЙКАМИ
excelsheets = excelappworkbook.Worksheets;
// Получаем ссылку на лист 1
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
#region Примеры: Выделение группы ячеек
//excelcells = excelworksheet.get_Range("A1", "С10");
// Тоже
//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
//excelcells = excelworksheet.get_Range("A1", "С10").Rows;
//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
// Одну ячейку
//excelcells = excelworksheet.get_Range("A1", "A1");
//excelcells = excelworksheet.get_Range("A1", Type.Missing);
// Десять строк с 1 по 10ю
//excelcells = (Excel.Range)excelworksheet.Rows["1:10", Type.Missing];
// Три столбца
//excelcells = (Excel.Range)excelworksheet.Columns["A:C", Type.Missing];
// Одну строку
//excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
// Один столбец
//excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
#endregion
// Выбираем первую ячейку
excelcells = excelworksheet.get_Range("A1", Type.Missing).Cells;
excelcells.Value2 = "Испольнитель";
excelcells = excelworksheet.get_Range("B1", Type.Missing).Cells;
excelcells.Value2 = "Трек";
excelcells = excelworksheet.get_Range("C1", Type.Missing).Cells;
excelcells.Value2 = "Длительность";
excelcells = excelworksheet.get_Range("D1", Type.Missing).Cells;
excelcells.Value2 = "Количество";
excelcells = excelworksheet.get_Range("A1", "D1").Cells;
excelcells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
#endregion
#region ЗАПИСЫВАЕМ ДАННЫЕ В ФАЙЛ
// Сбрасываем счётчик Хэша в начало
tableCountEnum.Reset();
// В цикле перебираем все записи Хэша
for (int i = 0; i < tableCount.Count; i++)
{
// Переход к следующей записи
tableCountEnum.MoveNext();
// Выделяем имя файла из пути к файлу
string trackName = Regex.Match(tableCountEnum.Key.ToString(), @"[^\\]*$").Value;
// Отрезаем расширение файла
trackName = trackName.Substring(0, trackName.Length - 4);
// Заменяем тире с пробелами на знак %
trackName = trackName.Replace("-", "%");
// Разделяем название группы и название трека
string[] fullName = trackName.Split('%');
// Работаем с документом
// Начинаем запись исполнителей со второй строки
int m = i + 2;
excelcells = (Excel.Range)excelworksheet.Cells[m, 1];
excelcells.Value2 = fullName[0].Trim();
excelcells = (Excel.Range)excelworksheet.Cells[m, 2];
if (fullName.Length > 1) {excelcells.Value2 = fullName[1].Trim(); }
excelcells = (Excel.Range)excelworksheet.Cells[m, 3];
excelcells.Value2 = tableDuration[tableCountEnum.Key];
excelcells = (Excel.Range)excelworksheet.Cells[m, 4];
excelcells.Value2 = tableCountEnum.Value.ToString();
}
#endregion
toolStripStatusLabel2.Text = "Сохранение документа.";
#region ЗАВЕРШАЕМ РАБОТУ С EXCEL
//Ссылку можно получить и так, но тогда надо знать имена книг,
//причем, после сохранения - знать расширение файла
//excelappworkbook=excelappworkbooks["Книга 1"];
//Запроса на сохранение для книги не должно быть
excelappworkbook.Saved = true;
// ФОрмат сохранения документа
excelapp.DefaultSaveFormat = Excel.XlFileFormat.xlWorkbookNormal;
// Сохраняем книгу
try
{
// Определяем имя путь сохранения файла
// Если каталог указан, проверяем его корректность и сохраняем файл
// Если каталог не указан или не существует, сохраняем отчёт в папке с программой
string saveFileName;
if (Directory.Exists(Settings.Default.ReportDir) && Settings.Default.ReportDir != "")
{
saveFileName = Settings.Default.ReportDir + "\\Report " +
DateTime.Now.ToShortDateString() + ".xls";
}
else
{
saveFileName = Application.StartupPath + "\\Report " +
DateTime.Now.ToShortDateString() + ".xls";
}
//excelappworkbook.Save();
excelappworkbook.SaveAs(saveFileName, Excel.XlFileFormat.xlWorkbookNormal);
MessageBox.Show("Отчёт успешно сгенерирован и сохранён в файл: " + saveFileName, "Готово",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка сохранения файла отчёта.",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Закрываем приложение
excelapp.Quit();
createReportButton.Enabled = true;
}
#endregion
toolStripStatusLabel2.Text = "";
} // End if DirectoryExists
else
{
// Выводим сообщение, если каталог отчётов не указан
MessageBox.Show("Каталог с файлами отчётов не найден!",
"Ошибка открытия каталога.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Определить есть ли такой файл Excel и попытаться открыть его
/// Если все успешно, то разблокировать элементы настройки выбора данных из Excel
/// Заполнить варианты выбора для excelLayoutComboBox
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FileNameChanged(object sender, EventArgs e)
{
CloseUsingExcel();
excelWorkSheets = new List <Excel._Worksheet>();
if (!String.IsNullOrEmpty(ExcelPath) && File.Exists(ExcelPath))
{
//Start Excel and get Application object.
OXL = new Excel.Application();
Excel.Workbooks workbooks = null;
Excel.Sheets sheets = null;
try
{
workbooks = OXL.Workbooks;
string ext = System.IO.Path.GetExtension(ExcelPath);
//if (ext != "csv")
//{
OWB = workbooks.Open(ExcelPath, 0, true);
//}
//else
//{
// OWB = workbooks.Open(ExcelPath, 0, true, Excel.XlFileFormat.xlCSVMSDOS,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, ";");
//}
if (OWB != null)
{
sheets = OWB.Worksheets;
foreach (Excel._Worksheet sheet in sheets)
{
excelWorkSheets.Add(sheet);
}
}
}
catch { }
finally
{
if (workbooks != null)
{
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
}
if (sheets != null)
{
//System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
}
}
excelSheetComboBox.ItemsSource = excelWorkSheets;
if (excelWorkSheets.Count > 0)
{
excelSheetComboBox.IsEnabled = true;
excelRowNumericUpDown.IsEnabled = true;
excelColComboBox.IsEnabled = true;
}
else
{
excelSheetComboBox.IsEnabled = false;
excelRowNumericUpDown.IsEnabled = false;
excelColComboBox.IsEnabled = false;
}
//Заполнить значения по умолчанию
try
{
//Для выбранного листа
if (!String.IsNullOrEmpty(defaultSheetName) && excelWorkSheets != null && excelWorkSheets.Count > 0)
{
Excel._Worksheet sheet = excelWorkSheets.Find(s => s.Name.Equals(defaultSheetName));
excelSheetComboBox.SelectedItem = sheet;
}
}
catch { }
}
protected virtual void Dispose(bool disposing)
{
workbook.Close(false);
ExcelApp.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(ExcelApp);
range = null;
worksheet = null;
sheets = null;
workbook = null;
workbooks = null;
ExcelApp = null;
}
static void Main(string[] args)
{
if (checkPing())
{
FileLocation fileLocation = null;
try
{
using (StreamReader sr = new StreamReader(Directory.GetCurrentDirectory() + @"\Input\Config.json"))
{
fileLocation = JsonConvert.DeserializeObject <FileLocation>(sr.ReadToEnd());
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("Connection Successful");
string str = "";
var Files = Directory.EnumerateFiles(fileLocation.SourceLocation, "*.*", SearchOption.AllDirectories)
.Where(s => s.EndsWith(".xlsx"));
Excel.Application xlApp = new Excel.Application();
bool refreshStatus = true;
foreach (string filePath in Files)
{
DirectoryInfo file = new DirectoryInfo(filePath);
Excel.Workbook wb = xlApp.Workbooks.Open(file.FullName);
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
Console.WriteLine("Refreshing : " + file.FullName);
Excel.Sheets excelSheets = wb.Worksheets;
foreach (Excel.Worksheet workSheet in excelSheets)
{
Console.WriteLine("SheetName: " + workSheet.Name);
Excel.PivotTables pivotTables = workSheet.PivotTables();
if (pivotTables.Count > 0)
{
foreach (Excel.PivotTable pivotTable in pivotTables)
{
Console.WriteLine(pivotTable.RefreshDate);
int attempts = 0;
Console.WriteLine("Refreshing " + pivotTable.Name);
for (; attempts < 3; attempts++)
{
refreshStatus = pivotTable.RefreshTable();
Console.WriteLine(refreshStatus);
if (refreshStatus == true)
{
break;
}
else
{
Console.WriteLine("Failed!! Reattempting...");
}
}
if (attempts == 3)
{
Console.WriteLine("All attempts exhausted! Failed.");
}
else
{
Console.WriteLine(pivotTable.RefreshDate);
}
}
}
else
{
Console.WriteLine("No Pivot Found in the Sheet!!");
}
}
if (refreshStatus)
{
Console.WriteLine("Refreshed :" + file.Name);
Console.WriteLine("Saving " + file.Name);
wb.SaveAs(fileLocation.DestinationLocation + file.Name);
}
wb.Close();
xlApp.Quit();
str = str + ", " + file.Name;
}
Console.WriteLine("Press Enter to Continue");
Console.ReadLine();
}
else
{
Console.WriteLine("Connection Unsuccessful");
}
}
public void Setup()
{
resourcePath = @"../../../../test.xlsx";
app = new Excel.Application();
var excelSheetPath = Path.GetFullPath(resourcePath);
wbs = app.Workbooks;
wb = wbs.Open(excelSheetPath);
wss = wb.Worksheets;
ws = (Excel.Worksheet)wss["Tasks"];
excelController = new ExcelController(resourcePath);
}
static OutputUnits makePerfMonGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.ChartObjects xlChart = null;
Excel.Range dataY = null;
Excel.Chart memChart = null;
Excel.Chart diskChart = null;
Excel.Chart cpuChart = null;
Excel.Chart netChart = null;
Excel.Axis xAxis = null;
OutputUnits csData = null;
bool leaking = false;
bool highcpu = false;
string exitFile = "";
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
dataSheet.get_Range("A2:A2", Type.Missing).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);//garbage row
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "results";
xlChart = (Excel.ChartObjects)newSheet.ChartObjects(Type.Missing);
memChart = xlChart.Add(20, 100, 450, 175).Chart;
diskChart = xlChart.Add(20, 280, 450, 175).Chart;
cpuChart = xlChart.Add(500, 100, 450, 175).Chart;
netChart = xlChart.Add(500, 280, 450, 175).Chart;
int rowTotal = dataSheet.UsedRange.Rows.Count;
int colTotal = dataSheet.UsedRange.Columns.Count;
dataSheet.get_Range("A2", "A" + rowTotal).NumberFormat = "m/d/yyyy h:mm";
string ttime = dataSheet.Cells[2, 1].Value.ToString();
Array availMem = (System.Array)dataSheet.get_Range("C2", "C" + rowTotal).Value;
Array cpuTotal = (System.Array)dataSheet.get_Range("D2", "D" + rowTotal).Value;
Array diskTotal = (System.Array)dataSheet.get_Range("B2", "B" + rowTotal).Value;
dataSheet.Cells[1, colTotal + 1] = "Total LAN (Bytes Total/Sec)";
double[] netties = new double[rowTotal - 1];
for (int i = 2; i <= rowTotal; i++)
{
if (colTotal > 5)
{
Array netLine = (System.Array)dataSheet.get_Range(xlStr(5) + i, xlStr(colTotal) + i).Value;
double netLineTotal = 0;
for (int j = 1; j <= netLine.Length; j++)
{
netLineTotal += Convert.ToDouble(netLine.GetValue(1, j));
}
netties[i - 2] = netLineTotal;
dataSheet.Cells[i, colTotal + 1] = netLineTotal;
}
else
{
dataSheet.Cells[i, colTotal + 1] = "0";
}
}
#region BuildCounters
double[] mems = ColToDouble(availMem);
double[] cpus = ColToDouble(cpuTotal);
double[] disks = ColToDouble(diskTotal);
//netties[]
double avgCPUs = cpus.Average();
PCounter CPU = new PCounter(avgCPUs, cpus.Max(), cpus.Min());
PCounter MEM = new PCounter(mems.Average(), mems.Max(), mems.Min());
PCounter DISK = new PCounter(disks.Average(), disks.Max(), disks.Min());
PCounter NETS = new PCounter(netties.Average(), netties.Max(), netties.Min());
if (avgCPUs > 40)
{
highcpu = true;
}
#endregion
#region leakCheck
double[] eqMB = new double[2];
List <double> memList = new List <double>();
int cX = availMem.Length;
for (int i = 1; i < rowTotal - 1; i++)
{
memList.Add(Convert.ToDouble(availMem.GetValue(i, 1)));
}
eqMB = LeastSquares(memList);
double stdD1 = StandardDev(memList);
List <double> memList2 = sigma(memList, stdD1, eqMB);
cX = memList2.Count();
eqMB = LeastSquares(memList2);
double stdD2 = StandardDev(memList2) * 1.2;
List <double> memList3 = sigma(memList2, stdD2, eqMB);
eqMB = LeastSquares(memList3);
if (eqMB[0] < 0)
{
leaking = true;
newSheet.get_Range("E4", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato);
}
#endregion
#region formatting
string lan = xlStr(colTotal + 1);
newSheet.get_Range("A1", Type.Missing).EntireColumn.ColumnWidth = 12;
newSheet.get_Range("A1", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignRight;
newSheet.get_Range("A2", Type.Missing).EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
newSheet.Cells[4, 5] = eqMB[0];
newSheet.Cells[2, 2] = "Avg";
newSheet.Cells[2, 3] = "Min";
newSheet.Cells[2, 4] = "Max";
newSheet.Cells[2, 5] = "Slope(3Sigma)";
newSheet.Cells[3, 1] = "CPU";
newSheet.Cells[3, 2] = "=AVERAGE(data!D:D)";
newSheet.Cells[3, 3] = "=MIN(data!D:D)";
newSheet.Cells[3, 4] = "=MAX(data!D:D)";
newSheet.Cells[4, 1] = "Avail.RAM";
newSheet.Cells[4, 2] = "=AVERAGE(data!C:C)";
newSheet.Cells[4, 3] = "=MIN(data!C:C)";
newSheet.Cells[4, 4] = "=MAX(data!C:C)";
newSheet.Cells[5, 1] = "LAN Usage";
newSheet.Cells[5, 2] = "=AVERAGE(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 3] = "=MIN(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 4] = "=MAX(data!" + lan + ":" + lan + ")";
newSheet.Cells[6, 1] = "Disk Usage";
newSheet.Cells[6, 2] = "=AVERAGE(data!B:B)";
newSheet.Cells[6, 3] = "=MIN(data!B:B)";
newSheet.Cells[6, 4] = "=MAX(data!B:B)";
#endregion
#region memChart
dataY = dataSheet.Range["C1", "C" + rowTotal];
memChart.SetSourceData(dataY, Type.Missing);
memChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
memChart.HasLegend = false;
xAxis = (Axis)memChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region diskChart
dataY = dataSheet.Range["B1", "B" + rowTotal];
diskChart.SetSourceData(dataY, Type.Missing);
diskChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
diskChart.HasLegend = false;
xAxis = (Axis)diskChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region cpuChart
dataY = dataSheet.Range["D1", "D" + rowTotal];
cpuChart.SetSourceData(dataY, Type.Missing);
cpuChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
cpuChart.HasLegend = false;
xAxis = (Axis)cpuChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region netChart
dataY = dataSheet.Range[xlStr(colTotal + 1) + "1", xlStr(colTotal + 1) + rowTotal];
netChart.SetSourceData(dataY, Type.Missing);
netChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
netChart.HasLegend = false;
xAxis = (Axis)netChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
string host = Path.GetFileNameWithoutExtension(dir + fm);
csData = new OutputUnits(host, ttime + " time chunks: " + (rowTotal - 1), CPU, MEM, NETS, DISK, leaking, highcpu);
exitFile = dir + fm;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@exitFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
//releaseObject(sC);
//releaseObject(myChart);
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
//using (StreamWriter outfile = File.AppendText("output.txt")) {
// outfile.WriteLine("Did have issues interacting with Excel on " + file);
//}
} finally {
releaseObject(xAxis);
releaseObject(dataY);
releaseObject(diskChart);
releaseObject(memChart);
releaseObject(cpuChart);
releaseObject(netChart);
releaseObject(xlChart);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
return(csData);
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
// Refrences to the first workbook to be diffed.
mBook1Sheets = null;
if (mBook1 != null)
{
mBook1.Close();
mBook1 = null;
}
// Refrences to the second workbook to be diffed.
mBook2Sheets = null;
if (mBook2 != null)
{
mBook2.Close();
mBook2 = null;
}
// Refrence to the instance of Microsoft Excel being used.
mExcel.Quit();
mExcel = null;
}
private void PrepareRecord()
{
objRecordExcel = new Excel.Application();
objRecordBooks = objRecordExcel.Workbooks;
if (File.Exists(txtStorage.Text))
objRecordBook = objRecordBooks.Open(txtStorage.Text);
else
objRecordBook = objRecordBooks.Add(true);
objRecordSheets = objRecordBook.Sheets;
objRecordSheet1 = objRecordSheets[1]; // 收件箱
objRecordSheet2 = objRecordSheets[1]; // 已发短信
bool bSheet1Exists = false, bSheet2Exists = false;
foreach (Excel.Worksheet objSheet in objRecordSheets)
{
if (objSheet.Name == "收件箱")
{
objRecordSheet1 = objSheet;
bSheet1Exists = true;
}
else if (objSheet.Name == "已发短信")
{
objRecordSheet2 = objSheet;
bSheet2Exists = true;
}
}
if (!bSheet1Exists)
{
objRecordSheet1 = objRecordSheets.Add();
objRecordSheet1.Name = "收件箱";
objRecordSheet1.Cells[1, 1] = "发件人";
objRecordSheet1.Cells[1, 2] = "短信内容";
objRecordSheet1.Cells[1, 3] = "发送时间";
}
if (!bSheet2Exists)
{
objRecordSheet2 = objRecordSheets.Add();
objRecordSheet2.Name = "已发短信";
objRecordSheet2.Cells[1, 1] = "收件人";
objRecordSheet2.Cells[1, 2] = "短信内容";
objRecordSheet2.Cells[1, 3] = "发送时间";
objRecordSheet2.Cells[1, 4] = "结果";
objRecordSheet2.Cells[1, 5] = "余额";
}
}
//1 - prvo učitamo stupce
public void DobijStupce()
{
ExcelObj = new Excel.Application();
excelApp = new Excel.Application();
theWorkbook = ExcelObj.Workbooks.Open(dat, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
sheets = theWorkbook.Worksheets;
worksheet = (Excel.Worksheet)sheets.get_Item(1);
excelApp.Workbooks.Open(dat);
for (int stupac = 1; stupac <= 25; stupac++)
{
var xlStupac = (worksheet.Cells[1, stupac] as Excel.Range).Value;
string xlStupac_str = Convert.ToString(xlStupac);
//za dobivanje stupca za kubike
if (xlStupac_str == "Total Gross Volume")
{
stupacCBM = stupac;
BrojStupca_CHR(stupacCBM);
textBoxCBM.Text = stupac_chr.ToString();
}
//else if (xlStupac_str.Contains("Volume"))
//{
// stupacCBM = stupac;
//}
//za dobivanje stupca za ZIP
else if (xlStupac_str == "Destination Postal Code")
{
stupacZIP = stupac;
BrojStupca_CHR(stupacZIP);
textBoxZIP.Text = stupac_chr.ToString();
}
//else if (xlStupac_str.Contains("Postal"))
// {
// stupacZIP = stupac;
// }
else if (xlStupac_str == null)
{
stupacCijena = stupac;
BrojStupca_CHR(stupacCijena);
textBoxCijene.Text = stupac_chr.ToString();
break;
}
}
}
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();
}
static void makeGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.Worksheet chartSheet = null;
Excel.Range range = null;
Excel.Range dataR = null;
int rowC = 0;
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "table";
chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
chartSheet.Name = "graph";
Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing);
dataR = dataSheet.UsedRange;
rowC = dataR.Rows.Count;
range = newSheet.get_Range("A1");
PivotCaches pCs = workbook.PivotCaches();
PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing);
PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1");
PivotField fA = pT.PivotFields("Time");
PivotField fB = pT.PivotFields("Command");
fA.Orientation = XlPivotFieldOrientation.xlRowField;
fA.Position = 1;
fB.Orientation = XlPivotFieldOrientation.xlColumnField;
fB.Position = 1;
pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum);
ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450);
Chart chartP = pChart.Chart;
chartP.SetSourceData(pT.TableRange1, Type.Missing);
chartP.ChartType = XlChartType.xlLine;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
using (StreamWriter outfile = File.AppendText("output.txt")) {
outfile.WriteLine("Did have issues interacting with Excel on " + file);
}
} finally {
/*Excel.Application excelApp = null;
* Excel.Workbook workbook = null;
* Excel.Sheets sheets = null;
* Excel.Worksheet dataSheet = null;
* Excel.Worksheet newSheet = null;
* Excel.Worksheet chartSheet = null;
* Excel.Range range = null;
* Excel.Range dataR = null;*/
releaseObject(dataR);
releaseObject(range);
releaseObject(chartSheet);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
}
/// <summary> Displays a 2DArray in excel. </summary>
/// <remarks> Tplateus, 3/01/2018. </remarks>
/// <param name="ListMatrix"> The 2DArray of entries. </param>
private void DisplayInExcel(string[,] ListMatrix, string[,] TableMatrix)
{
Excel.Application app = null;
Excel.Application openApp = null;
Excel.Workbooks books = null;
Excel.Workbooks openBooks = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheetList = null;
Excel.Worksheet sheetTable = null;
Excel.Range range = null;
Excel.Range rows = null;
Excel.Range cols = null;
List <string> errors = new List <string>();
try
{
try
{
openApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
openBooks = openApp.Workbooks;
for (int i = 1; i <= openBooks.Count; i++)
{
Excel.Workbook openBook = openBooks.Item[i];
Console.WriteLine(openBook.FullName);
if (openBook.FullName == outputfile)
{
errors.Add("The output file is already opened. Please close this file or choose a different output file.");
}
Marshal.ReleaseComObject(openBook);
}
}
catch
{
Console.WriteLine("No active Excel instance found. Starting an Excel instance...");
}
finally
{
if (openBooks != null)
{
Marshal.ReleaseComObject(openBooks);
}
if (openApp != null)
{
Marshal.ReleaseComObject(openApp);
}
}
app = new Excel.Application();
if (app == null)
{
errors.Add("No working instance of Excel could be found on this computer.");
}
if (errors.Count == 0)
{
app.SheetsInNewWorkbook = 2;
app.DisplayAlerts = false;
books = app.Workbooks;
book = books.Add();
sheets = book.Sheets;
sheetList = sheets.Item[1];
sheetTable = sheets.Item[2];
range = sheetList.Cells[1, 1];
sheetList.Name = "List parameters";
sheetTable.Name = "Table parameters";
#region format Sheet
//int rowCount = Matrix.GetLength(0);
//int columnCount = Matrix.GetLength(1);
//range = range.Resize[rowCount, columnCount];
//range.Value = Matrix;
//rows = range.Rows;
//cols = range.Columns;
////Set column widths to 300 pixels for each column in range.
//for (int iCol = 1; iCol <= columnCount; iCol++) //COMObject has a 1-based index.
//{
// Excel.Range column = cols[iCol];
// column.ColumnWidth = 32.56; //Translates to 300 pixels. Verify in Excel.
// if (column != null) Marshal.ReleaseComObject(column);
//}
//for (int iRow = 2; iRow <= rowCount; iRow++) // COMObject index starts at 1, not 0. First row is ignored (headers) => iRow = 2
//{
// Excel.Range row = rows.Item[iRow];
// object[,] objRow = row.Value2;
// List<string> listRow = objRow.Cast<string>().ToList(); //To utilize methods like findAll, the array is transformed to a list.
// for (int i = nParams; i < listRow.Count; i++) //First 3 columns are ignored since they dont have values (only ids).
// {
// string cellValue = listRow[i];
// List<string> allSameValues = listRow.FindAll(x => x == cellValue); //Search all cells with value == current cell value. If all values in the row are the same, its count should be the listRowCount -3 (ids).
// List<string> allValuesWithNotFound = listRow.FindAll(x => x == "!NOT_FOUND"); //!NOT_FOUND value is manually added in the 'TransformInto2DArray' method.
// if (allValuesWithNotFound.Count == 0)
// {
// if (allSameValues.Count < listRow.Count - nParams)
// {
// //Console.WriteLine(row.Cells[iRow, listRow.Count].Value2);
// Excel.Range value = row.Cells[1, i + 1];
// //Console.WriteLine(value.Value2);
// value.Interior.Color = Color.Red;
// value.Font.Color = Color.White;
// if (value != null) Marshal.ReleaseComObject(value);
// }
// }
// if (cellValue == "!NOT_FOUND")
// {
// Excel.Range cell = row.Cells[1, i + 1];
// cell.Interior.Color = Color.Orange;
// cell.Value2 = "";
// if (cell != null) Marshal.ReleaseComObject(cell);
// }
// }
// if (row != null) Marshal.ReleaseComObject(row);
//}
#endregion
FormatSheet(4, ListMatrix, sheetList);
FormatSheet(5, TableMatrix, sheetTable);
object missing = System.Reflection.Missing.Value;
book.SaveAs(outputfile, Excel.XlFileFormat.xlWorkbookDefault, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
book.Close(true, missing, missing);
app.SheetsInNewWorkbook = 3;
app.DisplayAlerts = true;
app.Quit();
string message = "The file '" + OutputFilenameBox.Text + ".xlsx' was succesfully placed in " + OutputDirBox.Text + ".";
MessageBox.Show(message, "Succes!");
}
else
{
DisplayErrors(errors);
}
}
finally
{
if (cols != null)
{
Marshal.ReleaseComObject(cols);
}
if (rows != null)
{
Marshal.ReleaseComObject(rows); Console.WriteLine("COMObject 'rows' released.");
}
if (range != null)
{
Marshal.ReleaseComObject(range); Console.WriteLine("COMObject 'range' released.");
}
if (sheetTable != null)
{
Marshal.ReleaseComObject(sheetTable); Console.WriteLine("COMObject 'sheetTable' released.");
}
if (sheetList != null)
{
Marshal.ReleaseComObject(sheetList); Console.WriteLine("COMObject 'sheetList' released.");
}
if (sheets != null)
{
Marshal.ReleaseComObject(sheets); Console.WriteLine("COMObject 'sheets' released.");
}
if (book != null)
{
Marshal.ReleaseComObject(book); Console.WriteLine("COMObject 'book' released.");
}
if (books != null)
{
Marshal.ReleaseComObject(books); Console.WriteLine("COMObject 'books' released.");
}
if (app != null)
{
Marshal.ReleaseComObject(app); Console.WriteLine("COMObject 'app' released.");
}
}
}
private unsafe void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook=1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < 15; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < 16; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < 512; j++)
{
progressBar2.Value = j;
if (Items[i,j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
fixed (Int64* buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
private void save_excel_Click(object sender, EventArgs e)
{
excelWorkbook.Close(null, null, null);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
sv_name = save_name.Text;
if (checkBox_multiple_run.Checked)
{
excelApp1 = new xc.Application();
excelApp1.Visible = true;
excelWorkbook1 = excelApp1.Workbooks.Open(Open_for_save.FileName);
excelSheets1 = excelWorkbook1.Worksheets;
excelWorkSheet1 = excelSheets1.get_Item("Sheet1");
excelCell1 = excelWorkSheet1.UsedRange;
for (int cc = 1; cc <= 16383; cc++)
{
ref_save_column = Convert.ToString(excelCell1.Cells[2, cc].Value2);
if (ref_save_column == null)
{
start_column_save = cc;
excelWorkSheet1.Cells[1, cc] = sv_name;
excelWorkSheet1.Cells[2, cc] = "Frame No.";
excelWorkSheet1.Cells[2, cc + 1] = "Flash period + Dark period";
excelWorkSheet1.Cells[2, cc + 2] = "Flash period";
break;
}
}
for (int bb = 1; bb <= box.GetUpperBound(0); bb++)
{
excelWorkSheet1.Cells[bb + 2, start_column_save] = (bb - 1) + 1;
excelWorkSheet1.Cells[bb + 2, start_column_save + 1] = box[bb - 1, 0];
excelWorkSheet1.Cells[bb + 2, start_column_save + 2] = box[bb - 1, 1];
}
excelWorkbook1.Save();
excelWorkbook1.Close(null, null, null);
excelApp1.Quit();
Marshal.ReleaseComObject(excelApp1);
excelApp1 = null;
GC.Collect();
GC.WaitForPendingFinalizers();
Array.Clear(box, 1, 51);
Array.Clear(box, 2, 2);
foreach (var series in show_freq_chart.Series)
{
series.Points.Clear();
}
Application.Idle += multiple_file_run;
Application.Idle -= save_excel_Click;
}
else
{
if (Open_for_save.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
excelApp1 = new xc.Application();
excelApp1.Visible = true;
excelWorkbook1 = excelApp1.Workbooks.Open(Open_for_save.FileName);
excelSheets1 = excelWorkbook1.Worksheets;
excelWorkSheet1 = excelSheets1.get_Item("Sheet1");
excelCell1 = excelWorkSheet1.UsedRange;
for (int cc = 1; cc <= 16383; cc++)
{
ref_save_column = Convert.ToString(excelCell1.Cells[2, cc].Value2);
if (ref_save_column == null)
{
start_column_save = cc;
excelWorkSheet1.Cells[1, cc] = sv_name;
excelWorkSheet1.Cells[2, cc] = "Frame No.";
excelWorkSheet1.Cells[2, cc + 1] = "Flash period + Dark period";
excelWorkSheet1.Cells[2, cc + 2] = "Flash period";
break;
}
}
for (int bb = 1; bb <= box.GetUpperBound(0); bb++)
{
excelWorkSheet1.Cells[bb + 2, start_column_save] = (bb - 1) + 1;
excelWorkSheet1.Cells[bb + 2, start_column_save + 1] = box[bb - 1, 0];
excelWorkSheet1.Cells[bb + 2, start_column_save + 2] = box[bb - 1, 1];
}
excelWorkbook1.Save();
excelWorkbook1.Close(null, null, null);
excelApp1.Quit();
Marshal.ReleaseComObject(excelApp1);
excelApp1 = null;
GC.Collect();
GC.WaitForPendingFinalizers();
Array.Clear(box, 1, 51);
Array.Clear(box, 2, 2);
foreach (var series in show_freq_chart.Series)
{
series.Points.Clear();
}
}
Application.Idle -= save_excel_Click;
}
}
private static void openExcel(String input, String output)
{
excelapp1 = new Microsoft.Office.Interop.Excel.Application();
myworkbook1 = excelapp1.Workbooks.Open(input, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
myworksheets1 = myworkbook1.Worksheets;
myworksheet1 = myworksheets1.get_Item(1);
range1 = myworksheet1.UsedRange;
excelapp2 = new Microsoft.Office.Interop.Excel.Application();
myworkbook2 = excelapp2.Workbooks.Open(output, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
myworksheets2 = myworkbook2.Worksheets;
myworksheet2 = myworksheets2.get_Item(1);
range2 = myworksheet2.UsedRange;
}
/// <summary>
/// 创建一个Excel程序实例
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
static void Main(string[] args)
{
String[] Color = new String[57];
int file_counter = 0;
Color[1] = "#000000";
Color[2] = "#FFFFFF";
Color[3] = "#FF0000";
Color[4] = "#00FF00";
Color[5] = "#0000FF";
Color[6] = "#FFFF00";
Color[7] = "#FF00FF";
Color[8] = "#00FFFF";
Color[9] = "#800000";
Color[10] = "#008000";
Color[11] = "#000080";
Color[12] = "#808000";
Color[13] = "#800080";
Color[14] = "#008080";
Color[15] = "#C0C0C0";
Color[16] = "#808080";
Color[17] = "#9999FF";
Color[18] = "#993366";
Color[19] = "#FFFFCC";
Color[20] = "#CCFFFF";
Color[21] = "#660066";
Color[22] = "#FF8080";
Color[23] = "#0066CC";
Color[24] = "#CCCCFF";
Color[25] = "#000080";
Color[26] = "#FF00FF";
Color[27] = "#FFFF00";
Color[28] = "#00FFFF";
Color[29] = "#800080";
Color[30] = "#800000";
Color[31] = "#008080";
Color[32] = "#0000FF";
Color[33] = "#00CCFF";
Color[34] = "#CCFFFF";
Color[35] = "#CCFFCC";
Color[36] = "#FFFF99";
Color[37] = "#99CCFF";
Color[38] = "#FF99CC";
Color[39] = "#CC99FF";
Color[40] = "#FFCC99";
Color[41] = "#3366FF";
Color[42] = "#33CCCC";
Color[43] = "#99CC00";
Color[44] = "#FFCC00";
Color[45] = "#FF9900";
Color[46] = "#FF6600";
Color[47] = "#666699";
Color[48] = "#969696";
Color[49] = "#003366";
Color[50] = "#339966";
Color[51] = "#003300";
Color[52] = "#333300";
Color[53] = "#993300";
Color[54] = "#993366";
Color[55] = "#333399";
Color[56] = "#333333";
char Context = ' ';
char SubContext = ' ';
string fileName = "";
string Path = "";
string Entity = "";
string Header = "";
string Footer = "";
StreamReader SR;
string S;
SR = File.OpenText("excel2xslt.config");
S = SR.ReadLine();
for (int i = 1; i <= 11; i++)
{
Console.WriteLine(S);
S = SR.ReadLine();
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 5) == "Files")
{
Path = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
}
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 5) == "Excel")
{
fileName = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
}
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Header")
{
Header = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
}
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Footer")
{
Footer = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
}
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 7) == "Context")
{
Context = Convert.ToChar(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""));
}
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 3) == "Sub")
{
SubContext = Convert.ToChar(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""));
}
if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Entity")
{
Entity = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
}
}
SR.Close();
if (Context == ' ')
{
Console.WriteLine("Context not defined. Please update the value in excel2xslt.config");
}
;
if (SubContext == ' ')
{
Console.WriteLine("SubContext not defined. Please update the value in excel2xslt.config");
}
;
if (fileName == "")
{
Console.WriteLine("FileName not defined. Please update the value in excel2xslt.config");
}
;
if (Path == "")
{
Console.WriteLine("Path not defined. Please update the value in excel2xslt.config");
}
;
if (Entity == "")
{
Console.WriteLine("Entity not defined. Please update the value in excel2xslt.config");
}
;
if (Header == "")
{
Console.WriteLine("Header not defined. Please update the value in excel2xslt.config");
}
;
if (Footer == "")
{
Console.WriteLine("Footer not defined. Please update the value in excel2xslt.config");
}
;
Excel.Application excelApp = new Excel.ApplicationClass(); // Creates a new Excel Application
excelApp.Visible = false; // visible
string workbookPath = Path + fileName;
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Console.WriteLine();
Console.WriteLine("Output files written to: " + Path);
StreamWriter swXMLCustomCfg = new StreamWriter(Path + "MailCustomCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));
StreamWriter swXMLHeaderCfg = new StreamWriter(Path + "MailHeaderCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));
StreamWriter swXMLConstants = new StreamWriter(Path + "Constants_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));
swXMLCustomCfg.WriteLine("<?xml version='1.0' encoding='ISO-8859-1' standalone='yes'?>");
swXMLCustomCfg.WriteLine("<ResultSet xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>");
swXMLHeaderCfg.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>");
swXMLHeaderCfg.WriteLine("<ResultSet>");
swXMLConstants.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>");
swXMLConstants.WriteLine("<root>");
// The following gets the Worksheets collection
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
//Console.WriteLine("No of sheets"+excelSheets.Count);
for (int s = 1; s <= excelSheets.Count; s++)
{
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(s);
StreamWriter sw = new StreamWriter(Path + excelWorksheet.Name + ".xsl", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));
swXMLConstants.WriteLine(" <Constant>");
swXMLConstants.WriteLine(" <DictDetail_Name Type='String'>" + excelWorksheet.Name + "</DictDetail_Name>");
swXMLConstants.WriteLine(" <DictDetail_ShortName Type='String'>" + excelWorksheet.Name + "</DictDetail_ShortName>");
swXMLConstants.WriteLine(" <ElementOwner Type='Character'>C</ElementOwner>");
swXMLConstants.WriteLine(" <DictCoherency_Name Type='String'>XSLTFileName</DictCoherency_Name>");
swXMLConstants.WriteLine(" <ReturnType Type='Character'>S</ReturnType>");
swXMLConstants.WriteLine(" <Description Type='String'/>");
swXMLConstants.WriteLine(" <Sorter Type='String'>XSLT</Sorter>");
swXMLConstants.WriteLine(" <StringValue Type='EmptyString'>" + excelWorksheet.Name + "</StringValue>");
swXMLConstants.WriteLine(" <Context Type='Character'>" + Context + "</Context>");
swXMLConstants.WriteLine(" </Constant>");
swXMLHeaderCfg.WriteLine(" <MailHeaderCfg>");
swXMLHeaderCfg.WriteLine(" <MailHeaderCfg_Name type='KDB_FIELD_NAME_0032'>" + excelWorksheet.Name + "</MailHeaderCfg_Name>");
swXMLHeaderCfg.WriteLine(" <Description type='string'/>");
swXMLHeaderCfg.WriteLine(" <CFG_Receiver type='string'>EDD_ThirdParty_Id_Cpty</CFG_Receiver>");
swXMLHeaderCfg.WriteLine(" <MailType_Name type='string'>Pdf Confirmation</MailType_Name>");
swXMLHeaderCfg.WriteLine(" <CFG_Master type='string'>EmptyEvent</CFG_Master>");
swXMLHeaderCfg.WriteLine(" <CFG_FaxAddress type='string'>DummyElement</CFG_FaxAddress>");
swXMLHeaderCfg.WriteLine(" <CFG_EmailFrom type='string'>DummyElement</CFG_EmailFrom>");
swXMLHeaderCfg.WriteLine(" <CFG_EmailTo type='string'>DummyElement</CFG_EmailTo>");
swXMLHeaderCfg.WriteLine(" <CFG_EmailCC type='string'>DummyElement</CFG_EmailCC>");
swXMLHeaderCfg.WriteLine(" <CFG_Bic type='string'>EVB_Bic_Id</CFG_Bic>");
swXMLHeaderCfg.WriteLine(" <ToBePrinted type='YesNo_t'>Y</ToBePrinted>");
swXMLHeaderCfg.WriteLine(" <NumberOfCopies type='KDB_FIELD_INTEGER'>1</NumberOfCopies>");
swXMLHeaderCfg.WriteLine(" <CFG_ExternalRef type='string'>NONE</CFG_ExternalRef>");
swXMLHeaderCfg.WriteLine(" <CFG_AddAddress type='string'>DummyElement</CFG_AddAddress>");
swXMLHeaderCfg.WriteLine(" <CFG_TypeOfOper type='string'>TAG22A</CFG_TypeOfOper>");
swXMLHeaderCfg.WriteLine(" <CFG_ReleaseDate type='string'>DummyElement</CFG_ReleaseDate>");
swXMLHeaderCfg.WriteLine(" <CFG_DeadLine type='string'>DummyElement</CFG_DeadLine>");
swXMLHeaderCfg.WriteLine(" <ValidityDateBegin type='KDB_FIELD_DATE'>2007/03/09</ValidityDateBegin>");
swXMLHeaderCfg.WriteLine(" <ValidityDateEnd type='KDB_FIELD_DATE'>2100/03/09</ValidityDateEnd>");
swXMLHeaderCfg.WriteLine(" <Priority type='BOPriority_t'>N</Priority>");
swXMLHeaderCfg.WriteLine(" <CFG_Entity type='string'>EBO_Entity_Id</CFG_Entity>");
swXMLHeaderCfg.WriteLine(" <AutoSending type='YesNo_t'>N</AutoSending>");
swXMLHeaderCfg.WriteLine(" <CFG_XSLT type='string'>" + excelWorksheet.Name + "</CFG_XSLT>");
swXMLHeaderCfg.WriteLine(" <Context type='BOContext_t'>" + Context + "</Context>");
swXMLHeaderCfg.WriteLine(" <SubContext type='BOContext_t'>" + SubContext + "</SubContext>");
swXMLHeaderCfg.WriteLine(" <CFG_Description type='string'>ModelDescripMails</CFG_Description>");
swXMLHeaderCfg.WriteLine(" <Entity_Name type='string'>" + Entity + "</Entity_Name>");
swXMLHeaderCfg.WriteLine(" <Validity type='BOValidity_t'>A</Validity>");
swXMLHeaderCfg.WriteLine(" </MailHeaderCfg>");
Excel.Range cells = (Excel.Range)excelWorksheet.get_Range("A1", excelWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing));
int[] actual_columns = new int[cells.Rows.Count + 1];
for (int i = 1; i <= cells.Rows.Count; i++)
{
actual_columns[i] = 0;
for (int j = 1; j <= cells.Columns.Count; j++)
{
Excel.Range current = (Excel.Range)cells[i, j];
if (current.Value2 != null)
{
actual_columns[i] = j;
}
;
}
;
//Console.WriteLine(actual_columns[i]);
}
;
sw.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>");
sw.WriteLine("<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform' xmlns:fo='http://www.w3.org/1999/XSL/Format' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:java='http://xml.apache.org/xslt/java' exclude-result-prefixes='java' xmlns:date='http://exslt.org/dates-and-times'");
sw.WriteLine(">");
sw.WriteLine(" <xsl:include href='" + Header + "'/>");
sw.WriteLine(" <xsl:include href='" + Footer + "'/> ");
sw.WriteLine("");
sw.WriteLine(" <xsl:template match='/'>");
sw.WriteLine("<fo:root xmlns:fo='http://www.w3.org/1999/XSL/Format'>");
sw.WriteLine(" <fo:layout-master-set>");
sw.WriteLine(" <fo:simple-page-master master-name='single_page' page-height='297mm' page-width='210mm' >");
sw.WriteLine(" <fo:region-body region-name='xsl-region-body' ");
sw.WriteLine(" margin='20mm' />");
sw.WriteLine(" </fo:simple-page-master>");
sw.WriteLine(" <fo:page-sequence-master master-name='repeatable_master'>");
sw.WriteLine(" <fo:repeatable-page-master-reference master-reference='single_page' maximum-repeats='10'/>");
sw.WriteLine(" </fo:page-sequence-master>");
sw.WriteLine(" </fo:layout-master-set>");
sw.WriteLine("");
sw.WriteLine(" <fo:page-sequence master-reference='repeatable_master'>");
sw.WriteLine(" <fo:flow flow-name='xsl-region-body'>");
sw.WriteLine(" ");
sw.WriteLine(" <fo:table font-family='Times New Roman' font-size='10.00pt' width='180.00mm'>");
sw.WriteLine(" <fo:table-column column-width='180mm'/>");
sw.WriteLine(" ");
sw.WriteLine(" <fo:table-body>");
sw.WriteLine(" <fo:table-row height='55mm'>");
sw.WriteLine(" <fo:table-cell padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>");
sw.WriteLine(" <xsl:call-template name='HEADER'/> ");
sw.WriteLine(" <fo:block height='20mm' text-align='left'></fo:block>");
sw.WriteLine(" </fo:table-cell>");
sw.WriteLine(" </fo:table-row>");
sw.WriteLine(" </fo:table-body>");
sw.WriteLine(" </fo:table>");
/* second table - body */
sw.WriteLine(" <fo:table font-family='Times New Roman' font-size='10.00pt' width='180.00mm'>");
for (int i = 1; i <= cells.Columns.Count; i++)
{
sw.WriteLine("<fo:table-column column-width='" + 180 / cells.Columns.Count + "mm'/>");
}
sw.WriteLine(" <fo:table-body>");
int[] total_columns = new int[cells.Rows.Count + 1];
string[] choose = new string[cells.Rows.Count + 1];
for (int i = 1; i <= cells.Rows.Count; i++)
{
total_columns[i] = 0;
Excel.Range current = (Excel.Range)cells[i, 1];
if (current.Value2 != null)
{
if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 6) == "choose")
{
choose[i] = "<xsl:choose>";
}
else
{
if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 4) == "when")
{
choose[i] = "<xsl:when test=\"" + (string)XsltFunctions.SubstringAfter(current.Value2.ToString(), "when ") + "\">";
/* if (XsltFunctions.Substring((string)XsltFunctions.SubstringAfter(current.Value2.ToString(),"when "),1,8) == "MailBody")
* {
* swXMLCustomCfg.WriteLine(" <MailCustomCfg>");
* swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>"+excelWorksheet.Name+"</MailHeaderCfg_Name>");
* swXMLCustomCfg.WriteLine(" <MailCustomTag type='string'>"+XsltFunctions.SubstringAfter(XsltFunctions.SubstringBefore((string)XsltFunctions.SubstringAfter(current.Value2.ToString(),"when ")," and "),"MailBody/")+"</MailCustomTag>");
* swXMLCustomCfg.WriteLine(" <Comment type='string'>/</Comment>");
* swXMLCustomCfg.WriteLine(" <CFG_Element type='string'>Empty</CFG_Element>");
* swXMLCustomCfg.WriteLine(" <SortingNum type='KDB_FIELD_INTEGER'></SortingNum>");
* swXMLCustomCfg.WriteLine(" </MailCustomCfg>");
* };
* if (XsltFunctions.Substring((string)XsltFunctions.SubstringAfter(current.Value2.ToString()," and "),1,8) == "MailBody")
* {
* swXMLCustomCfg.WriteLine(" <MailCustomCfg>");
* swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>"+excelWorksheet.Name+"</MailHeaderCfg_Name>");
* swXMLCustomCfg.WriteLine(" <MailCustomTag type='string'>"+XsltFunctions.SubstringAfter((string)XsltFunctions.SubstringAfter(current.Value2.ToString()," and "),"MailBody/")+"</MailCustomTag>");
* swXMLCustomCfg.WriteLine(" <Comment type='string'>/</Comment>");
* swXMLCustomCfg.WriteLine(" <CFG_Element type='string'>Empty</CFG_Element>");
* swXMLCustomCfg.WriteLine(" <SortingNum type='KDB_FIELD_INTEGER'></SortingNum>");
* swXMLCustomCfg.WriteLine(" </MailCustomCfg>");
* }
*/
}
else
{
if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 7) == "endwhen")
{
choose[i] = "</xsl:when>";
}
else
{
if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 9) == "endchoose")
{
choose[i] = "</xsl:choose>";
}
else
{
if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 9) == "otherwise")
{
choose[i] = "<xsl:otherwise>";
}
else
{
if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 12) == "endotherwise")
{
choose[i] = "</xsl:otherwise>";
}
else
{
choose[i] = "";
};
};
};
}
};
};
}
;
}
;
for (int i = 1; i <= cells.Rows.Count; i++)
{
int span = 1;
int mergedcellno = 0;
int t = 0;
if (choose[i] != "")
{
sw.WriteLine(choose[i]);
}
else
{
sw.WriteLine("<fo:table-row height='5mm'>");
for (int j = 1; j <= cells.Columns.Count && total_columns[i] < cells.Columns.Count; j++)
{
if (mergedcellno != 0 && t < mergedcellno && t != 0)
{
t = t + 1;
}
else
{
if (t >= mergedcellno)
{
t = 0;
mergedcellno = 0;
}
;
};
if (t == 0)
{
String color;
Excel.Range current = (Excel.Range)cells[i, j];
//Console.WriteLine("Color "+current.Interior.ColorIndex);
if ((int)current.Interior.ColorIndex >= 1 && (int)current.Interior.ColorIndex <= 56)
{
color = Color[(int)current.Interior.ColorIndex];
}
else
{
color = "white";
};
if (current.Value2 != null)
{
if ((bool)current.MergeCells)
{
//Console.WriteLine(current.get_Address("", "", Excel.XlReferenceStyle.xlR1C1,"", "")+"Row "+i+" Column "+j+" merged"+":"+current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1,"", ""));
span = Convert.ToInt32(XsltFunctions.SubstringAfter(XsltFunctions.SubstringAfter(current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1, "", ""), ":"), "C")) - Convert.ToInt32(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1, "", ""), "C"), ":")) + 1;
mergedcellno = span;
}
else
{
span = 1;
};
sw.WriteLine(" <fo:table-cell number-columns-spanned='"+ span + "' background-color='" + color + "' border-color='black' border-width='0.2pt' padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>");
sw.WriteLine(" <fo:block height='5mm' text-align='left'>");
if ((bool)XsltFunctions.Contains(current.Value2.ToString(), "MailBody") == true)
{
if ((bool)(XsltFunctions.Contains(current.Value2.ToString(), "Amount")) == true || (bool)(XsltFunctions.Contains(current.Value2.ToString(), "Rate")) == true)
{
sw.WriteLine(" <xsl:call-template name='Amount'>");
sw.WriteLine(" <xsl:with-param name='Amount' select='"+ current.Value2.ToString() + "'/>");
sw.WriteLine(" </xsl:call-template>");
}
else
{
if ((bool)(XsltFunctions.Contains(current.Value2.ToString(), "Date")) == true)
{
sw.WriteLine(" <xsl:call-template name='Date'>");
sw.WriteLine(" <xsl:with-param name='Date' select='"+ current.Value2.ToString() + "'/>");
sw.WriteLine(" </xsl:call-template>");
}
else
{
sw.WriteLine(" "+ "<xsl:value-of select = '" + current.Value2.ToString() + "'/>");
}
};
swXMLCustomCfg.WriteLine(" <MailCustomCfg>");
swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>" + excelWorksheet.Name + "</MailHeaderCfg_Name>");
swXMLCustomCfg.WriteLine(" <MailCustomTag type='string'>"+ XsltFunctions.SubstringAfter(current.Value2.ToString(), "MailBody/") + "</MailCustomTag>");
swXMLCustomCfg.WriteLine(" <Comment type='string'>/</Comment>");
swXMLCustomCfg.WriteLine(" <CFG_Element type='string'>Empty</CFG_Element>");
swXMLCustomCfg.WriteLine(" <SortingNum type='KDB_FIELD_INTEGER'></SortingNum>");
swXMLCustomCfg.WriteLine(" </MailCustomCfg>");
}
else
{
sw.WriteLine(" "+ current.Value2.ToString());
};
//Console.WriteLine(current.Value2);
sw.WriteLine(" </fo:block>");
sw.WriteLine(" </fo:table-cell>");
}
else
{
sw.WriteLine(" <fo:table-cell number-columns-spanned='"+ span + "' background-color='" + color + "' border-color='black' border-width='0.2pt' padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>");
sw.WriteLine(" <fo:block height='5mm' text-align='left'>");
sw.WriteLine(" </fo:block>");
sw.WriteLine(" </fo:table-cell>");
};
total_columns[i] = total_columns[i] + span;
span = 1;
}
if (mergedcellno != 0 && t < mergedcellno)
{
t = t + 1;
}
;
}
sw.WriteLine(" </fo:table-row>");
sw.WriteLine();
};
}
sw.WriteLine(" </fo:table-body>");
sw.WriteLine(" </fo:table>");
sw.WriteLine(" /* Placeholder for footer */");
sw.WriteLine(" ");
sw.WriteLine(" <fo:table font-family='Times New Roman' font-size='10.00pt' width='120.00mm'>");
sw.WriteLine(" <fo:table-column column-width='120mm'/>");
sw.WriteLine(" ");
sw.WriteLine(" <fo:table-body start-indent='0pt'>");
sw.WriteLine(" ");
sw.WriteLine(" <fo:table-row height='100mm'>");
sw.WriteLine(" <fo:table-cell padding-before='3pt' padding-after='3pt' padding-start='3pt' padding-end='3pt'>");
sw.WriteLine(" <xsl:call-template name='FOOTER'/>");
sw.WriteLine(" <fo:block height='20mm' text-align='left'> </fo:block>");
sw.WriteLine(" </fo:table-cell>");
sw.WriteLine(" </fo:table-row>");
sw.WriteLine(" </fo:table-body>");
sw.WriteLine(" </fo:table> ");
sw.WriteLine(" </fo:flow>");
sw.WriteLine(" </fo:page-sequence>");
sw.WriteLine("</fo:root>");
sw.WriteLine("");
sw.WriteLine("</xsl:template>");
sw.WriteLine("</xsl:stylesheet>");
sw.Close();
file_counter++;
Console.WriteLine(file_counter + ". XSL file successfully written to: " + excelWorksheet.Name + ".xsl");
}
swXMLCustomCfg.WriteLine("</ResultSet>");
swXMLCustomCfg.Close();
file_counter++;
Console.WriteLine(file_counter + ". MailCustomCfg file successfully written to: " + "MailCustomCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml");
swXMLHeaderCfg.WriteLine("</ResultSet>");
swXMLHeaderCfg.Close();
file_counter++;
Console.WriteLine(file_counter + ". MailHeaderCfg file successfully written to: " + "MailHeaderCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml");
swXMLConstants.WriteLine("</root>");
swXMLConstants.Close();
file_counter++;
Console.WriteLine(file_counter + ". Constants file successfully written to: " + "Constants_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml");
excelWorkbook.Close(false, 0, 0);
}
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 static void WriteResultsToExcel()
{
Console.WriteLine("Writing Results to Excel file...");
oXL = new Microsoft.Office.Interop.Excel.Application { Visible = true, DisplayAlerts = false };
string path = Path + "results.xls";
if (File.Exists(path))
{
File.Delete(path);
}
mWorkBook = oXL.Workbooks.Add();
mWorkSheets = mWorkBook.Worksheets;
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.Item["Sheet1"];
((Range)mWSheet1.Cells[1, 1]).EntireColumn.AutoFit();
mWSheet1.Cells[1, 1] = "File Size (Bytes)";
mWSheet1.Cells[1, 2] = "Encryption Time (microseconds)";
mWSheet1.Cells[1, 3] = "Decryption Time (microseconds)";
int index = 0;
for (int i = 2; i < NoOfFiles + 2; i++)
{
mWSheet1.Cells[i, 1] = _testDetails.ElementAt(index).FileSize;
mWSheet1.Cells[i, 2] = _testDetails.ElementAt(index).EncryptTime;
mWSheet1.Cells[i, 3] = _testDetails.ElementAt(index).DecryptTime;
index++;
}
}
private static void excelFile(System.Data.DataTable dtTable, List <Dictionary <string, object> > list)
{
// File save dialog
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.FileName = null;
saveFileDialog.Title = "Save path of the file to be exported";
//string filepath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application xlApp = null;
Excel.Workbooks wkbooks = null;
Excel.Workbook wkbook = null;
Excel.Sheets wksheets = null;
Excel.Worksheet wksheet = null;
try
{
xlApp = new Excel.Application();
wkbooks = xlApp.Workbooks;
wkbook = wkbooks.Add();
wksheets = wkbook.Sheets;
wksheet = wksheets.Add();
Console.WriteLine(list[0].Values);
wksheet.Name = "APPLE";
try
{
Console.WriteLine("It is working.");
for (var i = 0; i < dtTable.Columns.Count; i++)
{
wksheet.Cells[1, i + 1] = dtTable.Columns[i].ColumnName;
}
//rows
for (var i = 0; i < dtTable.Rows.Count; i++)
{
for (var j = 0; j < dtTable.Columns.Count; j++)
{
wksheet.Cells[i + 2, j + 1] = dtTable.Rows[i][j];
}
}
//wkbook.SaveAs(saveFileDialog, XlFileFormat.xlExcel8,false,
//false, false,false, XlSaveAsAccessMode.xlNoChange, Type.Missing,
//Type.Missing, Type.Missing,Type.Missing, Type.Missing);
Console.WriteLine("Processing!");
Console.WriteLine("File saved.");
wkbooks.Close();
wkbook.Close(false, Missing.Value, Missing.Value);
xlApp.Quit();
}
catch (Exception exp)
{
}
}
catch (Exception e)
{
//Console.WriteLine(e.ToString());
}
finally
{
if (wksheets != null)
{
Marshal.ReleaseComObject(wksheets);
}
if (wkbook != null)
{
Marshal.ReleaseComObject(wkbook);
}
if (wkbooks != null)
{
Marshal.ReleaseComObject(wkbooks);
}
if (xlApp != null)
{
Marshal.ReleaseComObject(xlApp);
}
}
}
private void otkritie()
{
try
{
string s = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
string we = System.IO.Path.Combine(s, @"ProgramData\FreeRooms");
System.IO.Directory.CreateDirectory(we);
excelapp = new Excel.Application();
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, numbernedeli),
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelapp.Visible = true;
}
catch { MessageBox.Show("Файл не был найден, обновите базы данных"); }
}
private void openWorkbook()
{
OpenFileDialog fileDialog = new OpenFileDialog();
string filepath;
fileDialog.Filter = "Excel|*.xls;*.xlsx|All Files|*.*";
fileDialog.Multiselect = false;
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filepath = fileDialog.FileName;
m_xlWorkbooks = m_xlApp.Workbooks;
m_xlWorkbook = m_xlWorkbooks.Open(filepath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
m_xlSheets = m_xlWorkbook.Worksheets;
m_xlWorksheets = new Dictionary<string, Excel.Worksheet>();
foreach (Excel.Worksheet sheet in m_xlSheets)
{
m_xlWorksheets.Add(sheet.Name, sheet);
}
comboBoxWorksheetSelector.DataSource = m_xlWorksheets.Keys.ToList<string>();
labelExcelWorkbookName.Text = "\"" + Path.GetFileName(filepath) + "\"";
}
}
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
static void Main(string[] args)
{
Console.WriteLine("Interop Assemblies Performance Test - 15000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet;
// do test 10 times
List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>();
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
sheet.UsedRange.ClearComments();
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 15000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
Excel.Font font = cellRange.Font;
font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = true;
Excel.Comment sampleComment = cellRange.AddComment("Sample Comment");
comReferencesList.Add(font as MarshalByRefObject);
comReferencesList.Add(sampleComment as MarshalByRefObject);
comReferencesList.Add(cellRange as MarshalByRefObject);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
foreach (var item in comReferencesList)
{
Marshal.ReleaseComObject(item);
}
comReferencesList.Clear();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-Interop.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}