private void btnReport_Click(object sender, EventArgs e)
{
ReportInfo();
if (dateRange.Count == 0 || dataRange.Count == 0)
{
MessageBox.Show("Нет данных");
return;
}
Excel.Application excelapp = new Excel.Application();
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(Type.Missing);
Excel.Sheets excelsheets = excelapp.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
Excel.Range excelcells = null;
int rows = dataRange.Count;
//head
excelcells = excelworksheet.Cells[1, 2];
excelcells.Clear();
excelcells.Value2 = "Прибыль";
for (int i = 1; i <= rows; i++)
{
excelcells = excelworksheet.Cells[i + 1, 1];
excelcells.Clear();
excelcells.Value2 = dateRange[i - 1];
}
for (int i = 1; i <= rows; i++)
{
excelcells = excelworksheet.Cells[i + 1, 2];
excelcells.Clear();
excelcells.Value2 = dataRange[i - 1];
}
Excel.Range c1 = excelworksheet.Cells[1, 1];
Excel.Range c2 = excelworksheet.Cells[dataRange.Count + 1, 2];
excelcells = excelworksheet.get_Range(c1, c2);
excelcells.Select();
Excel.Chart excelchart = (Excel.Chart)excelapp.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
excelchart.Activate();
excelchart.Select(Type.Missing);
excelchart.ChartTitle.Text = "Прибыль по месяцам";
excelapp.Visible = true;
}
/// <summary>
/// Reverts the changes done to Excel cell values after the last commit.
/// </summary>
/// <param name="refreshFromDb">Flag indicating if instead of reverting the data back to the way it was when the editing session started, it is pulled to have the most recent version of it.</param>
private void RevertDataChanges(bool refreshFromDb)
{
try
{
if (!refreshFromDb)
{
_mySqlTable.RejectChanges();
}
else
{
_mySqlTable.RefreshData();
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.EditDataRefreshErrorText, ex.Message);
}
Globals.ThisAddIn.SkipSelectedDataContentsDetection = true;
EditingWorksheet.UnprotectEditingWorksheet(EditingWorksheet_Change, WorksheetProtectionKey);
_editDataRange.Clear();
ExcelInterop.Range topLeftCell = _editDataRange.Cells[1, 1];
topLeftCell.Select();
_editDataRange = _mySqlTable.ImportDataIntoExcelRange(topLeftCell);
CommitChangesButton.Enabled = false;
AddNewRowToEditingRange(false);
}
private void DeleteXlData()
{
try
{
base.InitWorkSheet();
ExcelObj.Range xlRange = base.GetSheetRange(strDeleteRange);
if (xlRange != null)
{
xlRange.Clear();
base.SaveWorkBook(true);
}
else
{
base.ClearObject();
throw new Exception("Invalid Range");
}
}
catch (Exception ex)
{
base.ClearObject();
throw ex;
}
}
public override void Decorate(Excel.Worksheet worksheet, Schema flatSchema, object[,] tableDatas)
{
Excel.Range lastCell = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
if (MarginRowCount < lastCell.Row &&
MarginColumnCount < lastCell.Column)
{
Excel.Range tableRange = worksheet.Range[worksheet.Cells[MarginRowCount + 1, MarginRowCount + 1], lastCell];
tableRange.RowHeight = worksheet.StandardHeight;
tableRange.Clear();
}
WriteTableDatas(worksheet, tableDatas);
ApplyDropboxOnEnumCells(worksheet, flatSchema, tableDatas);
PaintOnRefErrorCells(worksheet, flatSchema, tableDatas);
DrawOutline(worksheet, tableDatas);
DecorateFieldRow(worksheet, flatSchema);
if (ShouldWriteDescriptions)
{
AddDescriptions(worksheet, flatSchema);
}
}
private void fOpenOKClick(Object sender, EventArgs e)
{
if (fopen.FileName == "")
{
lbFileName.Text = "Bạn chưa chọn File";
return;
}
// tạo app excel, mở workbook theo filename và sheet đầu tiên của workbook đó
lbFileName.Text = fopen.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();;
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(fopen.FileName);
Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = workSheet.UsedRange;
try
{
int rows = range.Rows.Count;
int cols = range.Columns.Count;
//get header
for (int c = 1; c <= cols; c++)
{
DataGridViewTextBoxColumn DGVColumn = new DataGridViewTextBoxColumn();
DGVColumn.Name = "Col" + c;
DGVColumn.HeaderText = range.Cells[1, c].Value.ToString();
DGVColumn.Visible = true;
DGVColumn.Frozen = false;
DGVColumn.ReadOnly = false;
dgvUser.Columns.Add(DGVColumn);
}
//get row
for (int r = 2; r < rows; r++)
{
DataGridViewRow dgvRow = new DataGridViewRow();
for (int c = 1; c <= cols; c++)
{
DataGridViewCell cell = new DataGridViewTextBoxCell();
cell.Value = range.Cells[r, c].Value.ToString();
dgvRow.Cells.Add(cell);
}
dgvUser.Rows.Add(dgvRow);
}
btnOK.Enabled = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
range.Clear();
workSheet = null;
workBook.Close();
app.Quit();
workBook = null;
}
}
private void Clearsheet(string Name)
{
Excel.Range rango = null;
Excel.Worksheet hoja = new Excel.Worksheet();
hoja = Globals.ThisAddIn.Application.Worksheets[Name];
hoja.Select();
rango = hoja.Range["A1", "K150"];
rango.Clear();
}
private void ExecuteFormat(Excel.Range newTargetRange, Excel.Range newMergeRange, Excel.Range newHeaderRange, Excel.Range templateRange)
{
newMergeRange.Clear();
templateRange.Copy();
newTargetRange.PasteSpecial(Excel.XlPasteType.xlPasteFormats);
foreach (Excel.Range cell in newHeaderRange)
{
cell.Formula = targetSheet.Range[$"A{cell.Row}"].Formula;
}
}
private static void Show_1_StructurePart_1(int lineCount, int depth, List <string> keys, Excel.Worksheet ws, int gap)
{
for (int i = 0; i < depth - 2; i++)
{
Excel.Range ran = (Excel.Range)ws.Cells[lineCount, i + 1];
ran.Value2 = $"[{keys[i]}]";
ran.Interior.Color = STRUCTUREPART_BACKCOLOR;
ran.Font.Color = STRUCTUREPART_FONTCOLOR;
}
if (depth < gap)
{
Excel.Range clearRange = ws.Range[ws.Cells[lineCount, depth - 1], ws.Cells[lineCount, gap - 1]];
clearRange.Clear();
}
}
/************** DELETE BUTTON ***************/
private void Delete_Click(object sender, RibbonControlEventArgs e)
{
Excel.Worksheet InputSheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[1];
Excel.Worksheet OutputSheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[4];
Excel.Worksheet ErrorSheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[6];
double size = InputSheet.Cells[7, 2].Value2;
Excel.Range OutRange = OutputSheet.Range[OutputSheet.Cells[2, 1], OutputSheet.Cells[(int)size + 1, 7]];
Excel.Range ErrorRange = ErrorSheet.Range[ErrorSheet.Cells[2, 1], ErrorSheet.Cells[(int)size + 1, 7]];
OutputSheet.Cells[1, 7].ClearContents();
OutRange.Clear();
ErrorRange.Clear();
}
public static Excel.Range Set(Excel.ListRow listRow, ColumnIndex columnIndex, string value)
{
if (null == listRow)
{
throw new ArgumentNullException("listRow");
}
Excel.Range range = listRow.Range[1, columnIndex];
if (string.IsNullOrWhiteSpace(value))
{
range.Clear();
range.Formula = string.Empty;
}
else
{
range.NumberFormatLocal = "@";
range.Value = value;
range.WrapText = true;
}
return(range);
}
public void ClearRange(ExcelInterop.Range from, ExcelInterop.Range to, ExcelInterop.Range with)
{
if (from == null)
{
return;
}
ExcelInterop.Worksheet concernedSheet = null;
bool isProtected = false;
try
{
concernedSheet = from.Worksheet;
isProtected = concernedSheet.ProtectContents;
if (isProtected)
{
concernedSheet.Unprotect(Type.Missing);
}
if (to == null)
{
to = concernedSheet.UsedRange;
}
from = from.Resize[to.Rows.Count - from.Rows.Count - 1, to.Columns.Count - from.Columns.Count - 1];
from.Clear();
if (with != null)
{
ExcelInterop.Interior withInterior = with.Interior;
ExcelInterop.Font withFont = with.Font;
ExcelInterop.Interior interior = from.Interior;
ExcelInterop.Font font = from.Font;
font.Color = withFont.Color;
interior.Color = withInterior.Color;
ExcelApplication.ReleaseComObject(interior);
ExcelApplication.ReleaseComObject(font);
ExcelApplication.ReleaseComObject(withInterior);
ExcelApplication.ReleaseComObject(withFont);
interior = null;
font = null;
withInterior = null;
withFont = null;
}
}
catch
{
if (concernedSheet != null)
{
ExcelApplication.ReleaseComObject(concernedSheet);
}
}
finally
{
if (concernedSheet != null && isProtected)
{
ProtectSheet(concernedSheet);
}
}
}
public static bool DataGridViewToExcel(ExportInfo exportInfo, DataGridView grid)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbooks workbooks = excel.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range range = (Excel.Range)worksheet.Columns;
try
{
worksheet.Name = "Sheet1";
range.NumberFormatLocal = "@"; //设置数字格式为文本
range.EntireColumn.AutoFit(); //自动调整列宽
int rowIndex = 0;
rowIndex += 1;
worksheet.Cells[rowIndex, 1] = exportInfo.Title;
rowIndex += 1;
worksheet.Cells[rowIndex, 1] = exportInfo.Filter;
rowIndex += 1;
worksheet.Cells[rowIndex, 1] = exportInfo.Counter;
rowIndex += 1;
//title
int c = 0;
foreach (DataGridViewColumn column in grid.Columns)
{
if (column.Visible != false)
{
c += 1;
range = (Excel.Range)worksheet.Columns[c];
range.ColumnWidth = column.Width / 8; //大概grid列宽除8
worksheet.Cells[rowIndex, c] = column.HeaderText;
}
}
//title Font Color Style
range = worksheet.Range[worksheet.Cells[rowIndex, 1], worksheet.Cells[rowIndex, c]];
range.Font.Bold = true;
range.Interior.ColorIndex = 15;
//rows
int r = rowIndex;
foreach (DataGridViewRow row in grid.Rows)
{
r += 1;
c = 0;
foreach (DataGridViewColumn column in grid.Columns)
{
if (column.Visible != false)
{
c += 1;
//worksheet.Cells[r, c] = Convert.ToString(row.Cells[column.Index].Value);
worksheet.Cells[r, c] = row.Cells[column.Index].Value;
}
}
}
//title + rows LineStyle
range = worksheet.Range[worksheet.Cells[rowIndex, 1], worksheet.Cells[r, c]];
range.Borders.LineStyle = 1;
excel.Visible = true; //显示EXCEL
return(true);
}
catch (Exception e)
{
range.Clear();
Marshal.ReleaseComObject(range);
range = null;
Marshal.ReleaseComObject(worksheet);
worksheet = null;
var saveChanges = false;
workbook.Close(saveChanges);
Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
Marshal.ReleaseComObject(workbooks);
workbooks = null;
excel.Quit();
Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect(); //强制垃圾回收。
throw new Exception(e.Message);
}
}
/// <summary>
/// Clears formulas, values and formatting from the range.
/// </summary>
/// <param name="range">The Excel range.</param>
/// <param name="attempts">The number of attempts.</param>
/// <param name="intervalMS">The interval between attempts. [ms].</param>
public static void RangeClear(Excel.Range range, int attempts = 10000, int intervalMS = 100)
{
Retry.DoTrue(() => TryBool(() => range.Clear()), new TimeSpan(intervalMS), attempts);
}
// Печатает таблицу без вставки текста
public static void ExportToExcelNew(Form form, DataGridView dgv, int fRow, int fColumn, string nameTable)
{
string baseDirectory = System.AppDomain.CurrentDomain.BaseDirectory;
string path = baseDirectory + "doc//Прайс-лист.xlsx";
int firstColumn = fColumn; // Первый столбец
int firstRow = fRow; // Первая строка
int lastRow = firstRow + dgv.RowCount; // Последняя строка
int lastColumn = firstColumn + dgv.ColumnCount; // Последний столбец
Excel.Application xl = new Excel.Application(); // Создаем экземпляр Excel'а
try
{
Excel.Workbook wb = (Excel.Workbook)xl.Workbooks.Open(path, false, false); // Открываем файл Excel
Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet; // Указываем ссылку на активный лист файла Excel
form.Cursor = Cursors.WaitCursor;
// Очистка старого форматирования
Excel.Range rngDEL = (Excel.Range)ws.Range[ws.Cells[1, 1], ws.Cells[7, dgv.ColumnCount + 4]];
rngDEL.Clear();
// Заполняем массив заголовками
object[] headers = new object[dgv.ColumnCount];
for (int i = 0; i < dgv.ColumnCount; i++)
{
headers[i] = dgv.Columns[i].HeaderText;
}
// Заполняем массив данными
object[,] rc = new object[dgv.RowCount, dgv.ColumnCount];
for (int i = 0; i < dgv.RowCount; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
rc[i, j] = dgv.Rows[i].Cells[j].Value;
}
}
// Вставка текста в ячейки шаблона
ws.Cells[1, 1] = DateTime.Now.ToShortDateString();
ws.Cells[3, 1] = nameTable;
// Форматирование
Excel.Range rngName = ws.Range[ws.Cells[3, 1], ws.Cells[3, 1]];
rngName.Font.Size = 16;
rngName.Font.Bold = true;
// Добавляем пустые строки со сдвигом вниз
Excel.Range insertColumn = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 2, lastColumn - 1]];
insertColumn.Insert(Excel.XlDirection.xlDown);
// Вставка рамок
Excel.Range pasteBorders = ws.Range[ws.Cells[firstRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]];
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
// Вставляем заголовки из массива в Excel
Excel.Range pasteCaptions = ws.Range[ws.Cells[firstRow - 1, firstColumn], ws.Cells[firstRow - 1, lastColumn]];
pasteCaptions.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, headers);
pasteCaptions.Font.Bold = true;
// Вставляем текст из массива в Excel
Excel.Range pasteText = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 1, lastColumn - 1]];
pasteText.Font.Bold = false;
pasteText.Font.Name = "Arial";
pasteText.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, rc);
// Удаляем последнюю пустую строку со сдвигом вверх
Excel.Range deleteRow = ws.Range[ws.Cells[lastRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]];
deleteRow.Delete(Excel.XlDirection.xlUp);
// Удаляем последний столбец
Excel.Range deleteColumn = ws.Range[ws.Cells[firstRow - 1, lastColumn], ws.Cells[lastRow, lastColumn]];
deleteColumn.Clear();
// Автоподбор ширины столбцов
Excel.Range rngAF = ws.Range[ws.Cells[firstRow - 1, 1], ws.Cells[lastRow, lastColumn]];
rngAF.Columns.AutoFit();
rngAF.Rows.AutoFit();
form.Cursor = Cursors.Default;
xl.Visible = true; // Делаем книгу Excel видимой
xl.UserControl = true; // Делаем книгу Excel активной
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n" + ex, "Ошибка");
}
}
public void clearRangeRow(int startIndex = 1, int endIndex = 300)
{
Excel.Range rng = xlWorksheet.Range[xlWorksheet.Cells[startIndex + 3, 1], xlWorksheet.Cells[endIndex + 3, 7]];
rng.Clear();
Marshal.ReleaseComObject(rng);
}
/// <summary>
/// 仕事シフトを個人シフトに変換します
/// </summary>
/// 仕事シフトをallJobRange、個人シフトをallIdvRangeとしておきます
/// 仕事シフトを文字列allStringに直して、個人シフト文字列allIdvStringに変換
/// 変換したものをallIdvRangeに入れ、さいごに必要に応じて結合を行う
public static void Run(Excel.Workbook book)
{
book.Application.ScreenUpdating = false;
book.Application.DisplayAlerts = false;
MainForm._MainFormInstance.inProrgamUse = true;
int Rows = 0, Columns = 0;
Excel.Worksheet jobsheet; //仕事シフト
Excel.Worksheet idvsheet; //個人シフト
Excel.Sheets sheets;
sheets = book.Worksheets;
jobsheet = (Excel.Worksheet)sheets.get_Item(sheets.getSheetIndex("仕事シフト"));
idvsheet = (Excel.Worksheet)sheets.get_Item(sheets.getSheetIndex("個人シフト"));
Excel.Range current = idvsheet.Cells[1, 1]; //セル単体です
string value;
int cellCount = 1;
Excel.Range wholeRange;
Stopwatch sw = new Stopwatch();
//仕事シフトのセル全体をallJobRangeに
Excel.Range allJobRange = jobsheet.Cells[MainForm._MainFormInstance.startaddr_row, MainForm._MainFormInstance.startaddr_col];
allJobRange = allJobRange.get_Resize(MainForm._MainFormInstance.jobtype + 10, 90 + 10);
//結合の解除と中身の破棄をやっておきます（名前や時間部分は消さないようにしているので注意）
Excel.Range allIdvRange = idvsheet.Cells[4, 5];
allIdvRange = allIdvRange.get_Resize(MainForm._MainFormInstance.jobtype + 10, 90 + 10);
allIdvRange.UnMerge();
allIdvRange.Clear();
//個人シフトのセル全体をallIdvRangeに
allIdvRange = idvsheet.Cells[1, 1];
allIdvRange = allIdvRange.get_Resize(MainForm._MainFormInstance.jobtype + 10, 90 + 10);
allIdvRange.Interior.ColorIndex = 2;
string[,] allString = allJobRange.DeepToString(); //仕事シフトを入れる
string[,] allIdvString = allIdvRange.DeepToString(); //個人シフトを入れる（何も起きないけど）
Excel.Range JobRange = jobsheet.Cells[MainForm._MainFormInstance.startaddr_row, MainForm._MainFormInstance.startaddr_col - 1];
JobRange = JobRange.get_Resize(MainForm._MainFormInstance.jobtype + 10, 1);
string[,] jobString = JobRange.DeepToString(); //仕事名を入れる
sw.Start();
for (Rows = 0; Rows < MainForm._MainFormInstance.jobtype; Rows++)
{
for (Columns = 0; Columns < 100; Columns++)
{
if (allString[Rows, Columns] == null || allString[Rows, Columns] == "")
{
continue;
}
else
{
for (int tmp = 0; tmp < MainForm._MainFormInstance.jobtype; tmp++)
{
if (allIdvString[tmp, 3] == null || allIdvString[tmp, 3] == "")
{
}
else if (allString[Rows, Columns] == allIdvString[tmp, 3])//仕事シフト文字列のとあるセル == 個人シフトの名前列の上からtmp番目
{
//以下緊急対応です
//配列外参照の可能性を消しました
if (Columns + 4 > 93)
{
break;
}
allIdvString[tmp, Columns + 4] = jobString[Rows, 0];//個人シフトのとあるセル = 仕事シフトの仕事列の上からRows番目
break;
}
}
}
}
}
allIdvRange.set_Value(Type.Missing, allIdvString);
book.Application.ScreenUpdating = true;
book.Application.DisplayAlerts = false;
//セルの結合を行う
for (Rows = 1; Rows < MainForm._MainFormInstance.jobtype; Rows++)
{
for (Columns = 1; Columns < 100; Columns++)
{
cellCount = 1;
value = allIdvString[Rows - 1, Columns - 1];
if (value == null || value == "")
{
}
else//ここが結合する必要のある部分です
{
while (value == allIdvString[Rows - 1, Columns])//いくつ結合するかを確認
{
cellCount++;
Columns++;
}
//wholeRangeで結合するセルを選択し、結合する
wholeRange = idvsheet.Cells[Rows, Columns - cellCount + 1];
wholeRange = wholeRange.get_Resize(1, cellCount);
book.Application.DisplayAlerts = false;
wholeRange.Merge();
wholeRange.Interior.ColorIndex = 35;
wholeRange.BorderAround2();
}
}
}
sw.Stop();
MessageBox.Show(sw.ElapsedMilliseconds + "ミリ秒で処理が終了しました．\r\n");
book.Application.ScreenUpdating = false;
book.Application.DisplayAlerts = true;
MainForm._MainFormInstance.inProrgamUse = false;
}
public DataSet GetExcel(string fileName, string sheetName)
{
try
{
// creat a Application object @"D:\FilesImport\ly_erp-Y1001-A0-01.xls"
oXL = new Excel.Application();
// get WorkBook object
oWB = oXL.Workbooks.Open(fileName, 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);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[sheetName];
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
DataSet ds = new DataSet();
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
for (int i = 1; i <= iValue; i++)
{
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
string strValue = oRng.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}
return(ds);
}
catch (Exception)
{
//return null;
throw;
}
finally
{
oRng.Clear();
GC.Collect();
oWB.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
//oWB.Close();
oXL.Quit();
Marshal.ReleaseComObject(oRng);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oSheet);
Marshal.FinalReleaseComObject(oXL);
oRng = null;
oWB = null;
oXL = null;
oSheet = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
Detail ws = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Hide ContainerList header for Amscan Q10, Q11
if (invoiceTable[0].IsContainerListNull())
{
Excel.Range rowh = ws.Range[ws.Cells[10, 17], ws.Cells[11, 17]];
rowh.Clear();
}
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 17]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 17];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].StoreName.Trim();
values[i, 1] = "'" + invoiceTable[i].StoreState;
values[i, 2] = "'" + invoiceTable[i].StoreZip;
values[i, 3] = "'" + invoiceTable[i].LocationCode;
values[i, 4] = invoiceTable[i].CtnQty;
values[i, 5] = invoiceTable[i].CartonRate;
values[i, 6] = invoiceTable[i].PltQty;
values[i, 7] = invoiceTable[i].PalletRate;
values[i, 8] = invoiceTable[i].Weight;
values[i, 9] = invoiceTable[i].RatedWeight;
values[i, 10] = invoiceTable[i].WeightRate;
values[i, 11] = invoiceTable[i].Surcharge;
values[i, 12] = invoiceTable[i].ConsolidationCharge;
values[i, 13] = invoiceTable[i].FuelRate;
values[i, 14] = invoiceTable[i].FuelSurcharge;
values[i, 15] = invoiceTable[i].DeliveryTotal;
values[i, 16] = !invoiceTable[i].IsContainerListNull() ? "'" + invoiceTable[i].ContainerList : "";
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 17]].Value2 = values;
#region Column Formats
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 2], ws.Cells[ROW0_DETAIL + rowCount - 1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 3], ws.Cells[ROW0_DETAIL + rowCount - 1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 4], ws.Cells[ROW0_DETAIL + rowCount - 1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 6], ws.Cells[ROW0_DETAIL + rowCount - 1, 6]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 6], ws.Cells[ROW0_DETAIL + rowCount - 1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 7], ws.Cells[ROW0_DETAIL + rowCount - 1, 7]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL, 7], ws.Cells[ROW0_DETAIL + rowCount - 1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]].NumberFormat = "#,##0_);(#,##0)";
ws.Range[ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]].NumberFormat = "#,##0_);(#,##0)";
ws.Range[ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]].NumberFormat = "#,##0.00_);(#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 12], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 12], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 13], ws.Cells[ROW0_DETAIL + rowCount - 1, 13]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 13], ws.Cells[ROW0_DETAIL + rowCount - 1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 14], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]].NumberFormat = "#,##0.0000_);(#,##0.0000)";
ws.Range[ws.Cells[ROW0_DETAIL, 14], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 15], ws.Cells[ROW0_DETAIL + rowCount - 1, 15]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 15], ws.Cells[ROW0_DETAIL + rowCount - 1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 16], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 16], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 17], ws.Cells[ROW0_DETAIL + rowCount - 1, 17]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
#endregion
Application.ScreenUpdating = true;
}