public void ExportCallQualityData(string formPath, string savePath, string fileFormat, DataTable dt, string contragent, string ocinyvach, string ocinyvanyi, string callType, string opDate, string criticalError, string filePath, string result)
{
try
{
Cursor.Current = Cursors.WaitCursor;
app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Open(formPath);
//Excel._Worksheet worksheet = null;
Excel._Worksheet worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Оцінка якості";
worksheet.Cells.Replace("{OpDateTime}", opDate);
worksheet.Cells.Replace("{Ochinuvanyy}", ocinyvanyi);
worksheet.Cells.Replace("{CallType}", callType);
worksheet.Cells.Replace("{Contragent}", contragent);
worksheet.Cells.Replace("{FilePath}", filePath);
worksheet.Cells.Replace("{Ocinyvach}", ocinyvach);
worksheet.Cells.Replace("{CriticalError}", criticalError);
worksheet.Cells.Replace("{Result}", result);
int startRow = 14;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i < dt.Rows.Count - 1)
{
worksheet.Rows[(startRow + i) + ":" + (startRow + i)].Copy();
worksheet.Rows[(startRow + i + 1) + ":" + (startRow + i + 1)].Insert();
}
worksheet.Range["B" + (startRow + i)].Value = dt.Rows[i][0].ToString();
worksheet.Range["D" + (startRow + i)].Value = dt.Rows[i][1].ToString();
worksheet.Range["E" + (startRow + i)].Value = dt.Rows[i][2].ToString();
}
worksheet.Rows[7 + ":" + (dt.Rows.Count - 1)].EntireRow().AutoFit();
if (fileFormat.Contains("xlsx"))
{
workbook.SaveAs(savePath);
app.Visible = true;
}
else if (fileFormat.Contains("pdf"))
{
workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, savePath);
workbook.Close(false);
app.Quit();
System.Diagnostics.Process.Start(savePath);
}
Cursor.Current = Cursors.Default;
//MessageBox.Show("Дані вивантажено!" + Environment.NewLine + savePath, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
if (app.Workbooks.Count == 1)
{
app.Quit();
}
app = null;
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void fileOpen()
{
try
{
mApp = new Excel.Application();
mApp.Visible = true;
//myBook = mApp.Workbooks.Add(filepath);
myBook = mApp.Workbooks.Open(filepath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, Type.Missing, Type.Missing);
sheets = myBook.Sheets;
mySheet = sheets[1];
if (mySheet == null)
{
Console.WriteLine("没有工作簿");
return;
}
mySheet.Activate();
rowCount = mySheet.UsedRange.Rows.Count + 1;
Console.WriteLine("加载时行数：" + rowCount);
status_textBox.Text = "文件初始化成功";
insertTitle();
}catch (Exception ex)
{
status_textBox.Text = ex.Message;
mApp.Quit();
}
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
// HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
}
System.GC.SuppressFinalize(this);
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
}
System.GC.SuppressFinalize(this);
}
/// <summary>
/// 释放
/// </summary>
public void Close()
{
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
}
public void Close()
{
try
{
excelApp.Quit();
}
catch { }
excelApp = null;
}
/// <summary>
/// Quit the current application
/// </summary>
protected void QuitApplication()
{
if (_application != null)
{
_application.Quit();
}
_application = null;
}
private static void ExcelClose(Excel._Workbook workbook, Excel._Application application)
{
var missingObject = System.Reflection.Missing.Value;
workbook.Close(false, missingObject, missingObject);
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
GC.Collect();
}
/// <summary>
/// Quit Excel app
/// </summary>
public static void Quit()
{
if (DebugFileSaveOperation)
{
return; // debug
}
if (LogCalls)
{
DebugLog.Message("ExcelOp Quit");
}
ReleaseAllObjects();
XlApp.Quit();
MessageFilter.Revoke();
//System.Windows.Forms.Application.DoEvents();
//Thread.Sleep(5000); // sleep to avoid race
//System.Windows.Forms.Application.DoEvents();
}
internal void CollectData(IEnumerable <string> years)
{
_excelApp = new ExcelApp.Application();
foreach (var year in years)
{
CollectDataForYear(year);
}
_excelApp.Quit();
}
/// <summary>
/// Closes the workbook and the excel application.
/// </summary>
/// <param name="application">Excel application object</param>
public static void Close(this InteropExcel._Application application)
{
if (application != null)
{
foreach (InteropExcel.Workbook workbook in application.Workbooks)
{
workbook.Close(false);
}
application.Quit();
}
}
public void Close()
{
GC.Collect();
// GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(ws);
wb.Close();
Marshal.ReleaseComObject(wb);
excel.Quit();
Marshal.ReleaseComObject(excel);
}
public ScanExcl(Microsoft.Office.Interop.Excel._Application excl, _Workbook wb, _Worksheet ws, Microsoft.Office.Interop.Excel.Range excel_range)
{
List <object> getters = new List <object>();
int count = 0, totalCells = excel_range.Rows.Count * excel_range.Columns.Count;
InitializeComponent();
t = new Thread(new ThreadStart(() =>
{
for (int i = 1; i <= wb.Worksheets.Count; i++)
{
for (int j = 1; j <= excel_range.Rows.Count; j++)
{
for (int k = 1; k <= excel_range.Columns.Count; k++)
{
count++;
if (excel_range.Cells[j, k] != null && excel_range.Cells[j, k].Value2 != null)
{
string tempStr = excel_range.Cells[j, k].Value2.ToString();
string[] tempArray = tempStr.Split(new char[] { ' ', '\n' },
StringSplitOptions.RemoveEmptyEntries);
foreach (var item in tempArray)
{
if (item.Contains("@"))
{
getters.Add(item);
}
}
}
Invoke(new System.Action(() =>
{
label1.Text = $"Просканировано ячеек {count} / {totalCells}";
progressBar1.Maximum = totalCells;
progressBar1.Value = count;
}));
}
}
}
wb.Close();
excl.Quit();
MessageWithData.Getter_Addresses = getters;
}));
t.IsBackground = true;
t.Start();
}
protected void SaveWorkBook(bool blnSave)
{
// save and close the workbook
if (this.xlWorkBook != null)
{
if (blnSave && this.IsWorkBookOpened)
{
xlWorkBook.Save();
}
xlWorkBook.Close(true, misValue, misValue);
this.IsWorkBookOpened = false;
this.IsWorkSheetOpened = false;
}
if (xlApp != null && this.IsAppOpened)
{
xlApp.Quit();
this.IsAppOpened = false;
}
// close the object
this.ClearObject();
}
public void Dispose()
{
try
{
if (m_ExcelApplication != null)
{
this.Close(false);
m_ExcelApplication.Quit();
m_ExcelApplication = null;
}
}
catch (Exception ex)
{
Debug.Write(ex.ToString());
}
}
private void frmBackUpToExcel_FormClosing(object sender, FormClosingEventArgs e)
{
//关闭EXCEL
if (app != null)
{
app.Quit();
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wrk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet);
app = null;
wrk = null;
objBooks = null;
objSheets = null;
objSheet = null;
GC.Collect();
}
public void closeFile()
{
try
{
if (myBook != null)
{
mApp.DisplayAlerts = false;
Console.WriteLine("关闭：" + filepath);
myBook.SaveAs(filepath, Missing.Value, Missing.Value, Missing.Value, false, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
mApp.DisplayAlerts = true;
myBook.Close(true, filepath, Missing.Value);
myBook = null;
mApp.Quit();
}
}
catch (Exception ex)
{
Console.WriteLine("异常：" + ex.Message);
}
}
public static void ExportData(DataTable dt)
{
try
{
Cursor.Current = Cursors.WaitCursor;
app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
int startRow = 0;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
worksheet.Cells[startRow + 1, i] = dt.Columns[i - 1].ColumnName;
}
Excel.Range oRange = worksheet.Range[worksheet.Cells[startRow + 2, 1], worksheet.Cells[startRow + 1 + dt.Rows.Count, dt.Columns.Count]];
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
arr[i, j] = dt.Rows[i][j];
}
}
oRange.Value = arr;
app.Visible = true;
Cursor.Current = Cursors.Default;
//MessageBox.Show("Дані вивантажено!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
if (app.Workbooks.Count == 1)
{
app.Quit();
}
app = null;
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Close()
{
if (xls_sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)xls_sheet);
xls_sheet = null;
}
if (xls_book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)xls_book);
xls_book = null;
}
if (xls_exp != null)
{
xls_exp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)xls_exp);
xls_exp = null;
}
System.GC.Collect();
}
/// <summary>
/// Закрывает открытые excel файлы
/// </summary>
public void CloseFiles()
{
try
{
workBook_1.Close(false, Type.Missing, Type.Missing);
workBook_2.Close(false, Type.Missing, Type.Missing);
excelApp_1.Workbooks.Close();
excelApp_2.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook_1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook_2);
excelApp_1.Quit();
excelApp_2.Quit();
}
catch (Exception)
{
}
finally
{
Process app_proc1 = GetExcelProcess(excelApp_1);
Process app_proc2 = GetExcelProcess(excelApp_2);
app_proc1.Kill();
app_proc2.Kill();
workSheet_1 = null;
workSheet_2 = null;
workBook_1 = null;
workBook_2 = null;
excelApp_1 = null;
excelApp_2 = null;
GC.Collect();
}
}
public void Close()
{
IntPtr T = new IntPtr(excelApp.Hwnd);
int k = 0;
SysCommon.ModExcel.GetWindowThreadProcessId(T, out k);
wbclass.Close(false, null, null);
excelApp.Application.Quit();
try
{
excelApp.Quit();
}
catch
{ }
excelApp = null;
try
{
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch
{ }
}
private void btnGen_Click(object sender, EventArgs e)
{
DateTime start = dtpStart.Value;
DateTime end = dtpEnd.Value;
DateTime cur;
int curMonth, curYear;
Object obj;
string path = txtPath.Text.Trim();
if (start > end)
{
MessageBox.Show("起始日期不能大于终止日期！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (path.Length < 1)
{
MessageBox.Show("请先选择存放备份文件的目录!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (!Directory.Exists(path))
{
MessageBox.Show("目录:" + txtPath.Text.Trim() + " 不存在!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
Directory.SetCurrentDirectory(path);
try
{
app = new Excel.Application();
}
catch
{
MessageBox.Show("本机没有安装EXCEL,无法进行此操作!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
app.Visible = false;
for (int i = 0; i < trvTables.Nodes.Count; i++)
{
if (!trvTables.Nodes[i].Checked)
{
continue;
}
//找对应的时间列
_sql = "select QUERY_COL from DMIS_SYS_TABLES where OWNER='" + cbbDataBase.SelectedItem.ToString() + "' and NAME='" + trvTables.Nodes[i].Text + "'";
obj = DBOpt.dbHelper.ExecuteScalar(_sql);
if (obj == null || obj.ToString().Trim() == "")
{
if (DBHelper.databaseType == "Oracle")
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text;
}
else if (DBHelper.databaseType == "SqlServer" || DBHelper.databaseType == "Sybase")
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + ".dbo." + trvTables.Nodes[i].Text;
}
else
{
_sql = "";
}
GenExcel(_sql, trvTables.Nodes[i].Text, "");
}
else
{
if (DBHelper.databaseType == "Oracle")
{
if (cbbTimeType.Text == "全部" || cbbTimeType.Text == "")
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where to_char(" + obj.ToString() + ",'YYYYMMDD')>='" + start.ToString("yyyyMMdd") + "' and to_char(" + obj.ToString() + ",'YYYYMMDD')<='" + end.ToString("yyyyMMdd") + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, start.ToString("yyyyMMdd") + "-" + end.ToString("yyyyMMdd"));
}
else if (cbbTimeType.Text == "按日")
{
cur = start;
while (cur <= end)
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where to_char(" + obj.ToString() + ",'YYYYMMDD')='" + cur.ToString("yyyyMMdd") + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, cur.ToString("yyyyMMdd"));
cur = cur.AddDays(1);
}
}
else if (cbbTimeType.Text == "按月")
{
cur = start;
curMonth = Convert.ToInt32(start.ToString("yyyyMM"));
while (curMonth <= Convert.ToInt32(end.ToString("yyyyMM")))
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where to_char(" + obj.ToString() + ",'YYYYMM')='" + cur.ToString("yyyyMM") + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, cur.ToString("yyyyMM"));
cur = cur.AddMonths(1);
curMonth = Convert.ToInt32(cur.ToString("yyyyMM"));
}
}
else if (cbbTimeType.Text == "按年")
{
cur = start;
curYear = start.Year;
while (curYear <= end.Year)
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where to_char(" + obj.ToString() + ",'YYYY')='" + cur.Year + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, cur.ToString("yyyy"));
cur = cur.AddYears(1);
curYear = cur.Year;
}
}
}
else if (DBHelper.databaseType == "SqlServer" || DBHelper.databaseType == "Sybase")
{
if (cbbTimeType.Text == "全部")
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where convert(char(8)," + obj.ToString() + ",112)>='" + start.ToString("yyyyMMdd") + "' and convert(char(8)," + obj.ToString() + ",112)<='" + end.ToString("yyyyMMdd") + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, start.ToString("yyyyMMdd") + "-" + end.ToString("yyyyMMdd"));
}
else if (cbbTimeType.Text == "按日")
{
cur = start;
while (cur <= end)
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where convert(char(8)," + obj.ToString() + ",112)='" + cur.ToString("yyyyMMdd") + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, cur.ToString("yyyyMMdd"));
cur = cur.AddDays(1);
}
}
else if (cbbTimeType.Text == "按月")
{
cur = start;
curMonth = Convert.ToInt16(start.ToString("yyyyMM"));
while (curMonth <= Convert.ToInt16(end.ToString("yyyyMM")))
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where convert(char(6)," + obj.ToString() + ",112)='" + cur.ToString("yyyyMM") + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, cur.ToString("yyyyMM"));
cur = cur.AddMonths(1);
curMonth = Convert.ToInt16(cur.ToString("yyyyMM"));
}
}
else if (cbbTimeType.Text == "按年")
{
cur = start;
curYear = start.Year;
while (curYear <= end.Year)
{
_sql = "select * from " + cbbDataBase.SelectedItem.ToString() + "." + trvTables.Nodes[i].Text + " where convert(char(4)," + obj.ToString() + ",112)='" + cur.Year + "'";
GenExcel(_sql, trvTables.Nodes[i].Text, cur.ToString("yyyy"));
cur = cur.AddYears(1);
curYear = cur.Year;
}
}
}
else
{
_sql = "";
}
}
}
//关闭EXCEL进程
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wrk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet);
app = null;
wrk = null;
objBooks = null;
objSheets = null;
objSheet = null;
GC.Collect();
}
public Result Execute(
ExternalCommandData commandData,
ref string message,
ElementSet elements)
{
UIApplication uiapp = commandData.Application;
UIDocument uidoc = uiapp.ActiveUIDocument;
Application app = uiapp.Application;
Document doc = uidoc.Document;
// creating bfs document instance
BfsDocument bfsDocument = new BfsDocument(doc);
// collecting schedules data from Revit
List <List <List <string> > > schedulesData = getSchedulesData(doc);
List <List <List <string> > > revisionData = getRevisionData(doc);
if (schedulesData == null || revisionData == null)
{
return(Result.Cancelled);
}
// asking file name to save
System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog();
saveFileDialog.InitialDirectory = doc.PathName;
saveFileDialog.Title = "Salvar";
saveFileDialog.DefaultExt = ".xlsx";
saveFileDialog.Filter = "Excel file|*.xlsx";
saveFileDialog.FileName += string.Format("{0}-QTD-Rev.{1}", bfsDocument.Codigo, bfsDocument.Revisao);
saveFileDialog.OverwritePrompt = false;
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
// creating export to excel instance
ExportToExcel exportToExcel = new ExportToExcel(
saveFileDialog.FileName,
"Planilha de quantidades",
schedulesData,
revisionData);
// solving export
exportToExcel.Export(ExportToExcel.TemplateType.QUANTIDADES, bfsDocument);
// running export() and getting the application created
Excel._Application oXL = exportToExcel.Application;
Excel._Workbook oWB = exportToExcel.Workbook;
oXL.UserControl = false;
try
{
// saving and closing
oWB.SaveAs(saveFileDialog.FileName, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("O arquivo a ser substituído está aberto.", "Erro");
return(Result.Failed);
}
oWB.Close();
oXL.Quit();
// launching the file
System.Diagnostics.Process.Start(saveFileDialog.FileName);
return(Result.Succeeded);
}
return(Result.Cancelled);
}
public void Close()
{
excelApp.Quit();
excelApp = null;
}
protected void QuitApplication()
{
Application.Quit();
}
public void ExportData(DataGridView dgv, string[] titleArr = null)
{
try
{
Cursor.Current = Cursors.WaitCursor;
app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
int startRow = 0;
if (titleArr != null)
{
foreach (string str in titleArr)
{
string strTemp = str;
if (str.Contains("{DATE}"))
{
strTemp = str.Replace("{DATE}", Convert.ToDateTime(dgv.Rows[0].Cells["Дані_станом_на"].Value).ToShortDateString());
}
worksheet.Cells[startRow + 1, 1] = strTemp;
startRow++;
}
}
for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
worksheet.Cells[startRow + 1, i] = dgv.Columns[i - 1].HeaderText;
}
Excel.Range oRange = worksheet.Range[worksheet.Cells[startRow + 2, 1], worksheet.Cells[startRow + 1 + dgv.Rows.Count, dgv.Columns.Count]];
object[,] arr = new object[dgv.Rows.Count, dgv.Columns.Count];
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
//if (dgv.Columns[j].ValueType.ToString().Equals("System.String"))
// arr[i, j] = dgv.Rows[i].Cells[j].Value.ToString().Replace("\r\n", "");
//else
// arr[i, j] = dgv.Rows[i].Cells[j].Value;
if (dgv.Columns[0].HeaderText == "Файл")
{
if (dgv.Rows[i].Cells[j].Value != null)
{
arr[i, j] = j == 0 ? "=HYPERLINK(\"" + dgv.Rows[i].Cells[j].Value.ToString() + "\",\"" + dgv.Rows[i].Cells[j].Value.ToString() + "\")" : dgv.Rows[i].Cells[j].Value;
}
else
{
arr[i, j] = "";
}
}
else
{
arr[i, j] = dgv.Rows[i].Cells[j].Value != null ? dgv.Rows[i].Cells[j].Value : "";
}
}
}
oRange.Value = arr;
int lastCol = worksheet.Range["A" + startRow + 1].End[Excel.XlDirection.xlToRight].Column;
worksheet.Range[worksheet.Cells[startRow + 1, 1], worksheet.Cells[startRow + 1, lastCol]].AutoFilter();
worksheet.Range[worksheet.Cells[startRow + 1, 1], worksheet.Cells[startRow + 1, lastCol]].WrapText = true;
worksheet.Range[worksheet.Cells[startRow + 1, 1], worksheet.Cells[startRow + 1, lastCol]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Range[worksheet.Cells[startRow + 1, 1], worksheet.Cells[startRow + 1, lastCol]].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
worksheet.Range["A3", "E3"].Interior.Color = Color.PaleGreen; // колір клітинки
worksheet.Range["A3", "E3"].Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; // рамки в клітинці
//worksheet.Columns["1:" + lastCol].AutoFit();
oRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[startRow + 1 + dgv.Rows.Count, dgv.Columns.Count]];
oRange.Columns.AutoFit();
app.Visible = true;
string fileName = dgv.Tag != null?dgv.Tag.ToString() : "";
if (fileName.Length > 0)
{
DateTime curDate = Convert.ToDateTime(dgv.Rows[0].Cells["Дані_станом_на"].Value);
if (fileName.Contains("Актуалізація"))
{
string path = @"S:\DATA\SPV\03_Актуалізація\AutoCreate\" + curDate.ToString("yyyy.MM.dd") + @"\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
workbook.SaveAs(path + fileName.Replace("{DATE}", curDate.ToShortDateString()) + ".xlsx");
}
else
{
workbook.SaveAs(fileName.Replace("{DATE}", curDate.ToShortDateString()) + ".xlsx");
}
MessageBox.Show("Файл збережено за посиланням: \n" + workbook.Path, "Вивантажено", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//MessageBox.Show("Дані вивантажено!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
if (app.Workbooks.Count == 1)
{
app.Quit();
}
app = null;
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor.Current = Cursors.Default;
}
}