private static void PresentResult(Query query, Report report, int profileCounter, int cellOffset)
{
// This method shall include a verification of empty columns. If the user has selected more than one profile
// the second query shall be presented on the right of the first query result in Excel.
Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application;
Worksheet activeSheet = currentApp.ActiveSheet as Worksheet;
if (currentApp.ActiveSheet != null)
{
int dataLength = 2;
if (report.Data != null)
{
dataLength = report.Data.GetLength(1);
}
int activeRow = currentApp.ActiveCell.Row;
int activeColumn = currentApp.ActiveCell.Column + cellOffset;
/*if (currentApp.ActiveCell.Cells.Value2 != null)
* {
* activeColumn = ActiveColumn(profileCounter, dataLength);
* }*/
object[] queryInformation = GetQueryInformation(query, report, profileCounter);
int infoRows = queryInformation.GetLength(0);
if (report.Data != null)
{
dataLength = report.Data.GetLength(1);
Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn],
activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]);
dataRange.Value2 = report.Data;
Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn],
activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]);
headerRange.Value2 = report.Headers;
headerRange.Font.Bold = true;
}
Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn],
activeSheet.Cells[activeRow, activeColumn + dataLength - 1]);
queryInformationRange.Font.Italic = true;
queryInformationRange.MergeCells = true;
queryInformationRange.Borders.Weight = XlBorderWeight.xlThin;
queryInformationRange.Value2 = queryInformation;
//int height = (int)queryInformationRange.Height; queryInformationRange.RowHeight = height;
}
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat(Format: myFormat);
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 20;
excel.Cells[1, 1] = "INFORME DE VENTAS POR CATEGORIAS";
excel.Cells[2, 1] = "DESDE " + sfechaInicio + " A " + sfechaFin;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
if (iIndiceColumna == 1)
{
excel.Cells[1, iIndiceColumna].ColumnWidth = 40;
}
excel.Cells[5, iIndiceColumna] = col.HeaderText;
excel.Cells[5, iIndiceColumna].Interior.Color = Color.Yellow;
}
int iIndiceFila = 5;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
excel.Cells[iIndiceFila + 1, iIndiceColumna].BorderAround();
}
}
excel.get_Range("A5", "C5").BorderAround();
excel.Visible = true;
}
//生成Excel
private bool ExportToExcel(DataTable dt)
{
bool result = false;
this.UseWaitCursor = true;
if (dt.Rows.Count > 65536)
{
MessageBox.Show("數據記錄太多(最多不能超過65536條)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return result;
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
labMsg.Text = "Excel正在匯出中…………";
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机器未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(true);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "過膠機生產日報表";
//worksheet.QueryTables.Add
#region worksheet的格式
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印
worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印
worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效
worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁
worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中
worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102);
worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126);
worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126);
xlApp.Visible = false;
#endregion
#region 列名
#region 循環填充列名，很慢，已經REMARK
//for (i = 1; i <= dt.Columns.Count - 1; i++)
//{
// if (i == 11 || i == 12 || i == 13)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3);
// }
// else if (i == 14 || i == 15 || i == 16 || i == 17)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8);
// }
// else if (i >= 23 && i <= 31)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim();
// }
// else
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName;
// }
//}
#endregion
int RowIndex = 2;
MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0);
RowIndex = RowIndex + 1;
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9);
#endregion
#region 填充數據 向Excel中逐行逐列写入表格中的数据
RowIndex = RowIndex + 1; //數據開始的行號
int ExcelCurrectRow = RowIndex; //當前行
int dtRowNO;
int realRowNO = 0;//dt實際行
int dtKeyRowNO = RowIndex;//NO:4
int megreCount = 0;
int dtMetrialRowNO = 23;//物料編號所在列
int tempRow;
int tempCol;
#region 較慢的填充數據方法 已經REMARK
//for (int row = 0; row < dt.Rows.Count; row++)
//{
// //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString());
// for (int col = 1; col < dt.Columns.Count; col++)
// {
// if (row > 0) //班次內工程ID相等 col22之後不合併
// {
// if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22)
// {
// CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true);
// }
// else
// {
// CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
// xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
// if (col < 22) currectRow = RowIndex;
// }
// }
// else
// {
// CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
// xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
// }
// }
// RowIndex++;
// //if (RowIndex == 15) goto range; // for 測試
//}
#endregion
DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" });
for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++)
{
DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' ");
megreCount = currectKeyRow.Length;
for (tempRow = 0; tempRow < megreCount; tempRow++)
{
if (tempRow == 0)
{
for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true);
}
realRowNO += megreCount;
}
for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false);
}
dtKeyRowNO++;
}
//dtKeyRowNO++;
//if (realRowNO > dt.Rows.Count) break;
/*
* DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" });
List<Thread> threads = new List<Thread>();
for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++)
{
*/
}
#endregion
#region 設置Sheet邊框
//range:
Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]);
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
#endregion
Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]);
DataFormat.Font.Name = "楷体";
DataFormat.Font.Size = 10;
Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]);
IntFormat.NumberFormatLocal = "0.00";
#region savecode
Object missing = Missing.Value;
SaveFileDialog saveForm = new SaveFileDialog();
saveForm.Filter = "Execl files (*.xls)|*.xls";
saveForm.FilterIndex = 0;
saveForm.FileName = "過膠機生產日報表";
if (saveForm.ShowDialog() == DialogResult.OK)
{
string fileName = saveForm.FileName;
workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
#endregion
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
labMsg.Text = "";
this.UseWaitCursor = false;
xlApp.Quit();
IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
System.GC.Collect();
}
labMsg.Text = "";
return result;
}
private void btnToExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (this.dgvShowData.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = true;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
//寫入欄位名稱
for (int i = 0; i < dgvShowData.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvShowData.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvShowData.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvShowData.Columns.Count; j++)
{
if (dgvShowData[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvShowData[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvShowData[j, i].Value.ToString();
}
}
}
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit(); //自動調整列高
excel.Cells.EntireColumn.AutoFit();
excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
excel.get_Range("A1").Font.Color = Color.White;
excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
excel.get_Range("B1").Font.Color = Color.White;
excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
excel.get_Range("C1").Font.Color = Color.White;
excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
excel.get_Range("D1").Font.Color = Color.White;
excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
excel.get_Range("E1").Font.Color = Color.White;
excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
excel.get_Range("F1").Font.Color = Color.White;
excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
excel.get_Range("G1").Font.Color = Color.White;
excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
excel.get_Range("H1").Font.Color = Color.White;
excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
excel.get_Range("I1").Font.Color = Color.White;
excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
excel.get_Range("J1").Font.Color = Color.White;
excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
excel.get_Range("K1").Font.Color = Color.White;
excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
excel.get_Range("L1").Font.Color = Color.White;
excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
excel.get_Range("M1").Font.Color = Color.White;
excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
excel.get_Range("N1").Font.Color = Color.White;
excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
excel.get_Range("O1").Font.Color = Color.White;
excel.get_Range("O1").Interior.Color = Color.DodgerBlue;
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
MessageBox.Show("已成功輸出至Excel！", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
}
private static void PresentResultUpdate(Query query, Report report, int profileCounter)
{
Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application;
Worksheet activeSheet = currentApp.ActiveSheet as Worksheet;
if (currentApp.ActiveSheet != null)
{
int activeColumn = query.Column;
int activeRow = query.Row;
string addressLocal = "";
string addressHelper = "";
object[] queryInformation = GetQueryInformation(query, report, profileCounter);
int infoRows = queryInformation.GetLength(0);
int dataLength = dataLength = report.Data.GetLength(1);
Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn],
activeSheet.Cells[activeRow, activeColumn + dataLength - 1]);
//
addressLocal = queryInformationRange.get_Address().Replace("$", "");
string[] adLocal = addressLocal.Split(':');
// Looks after other reports in the same column.
foreach (string address in _addressQueries)
{
addressHelper = address.Replace("$", "");
// If there exist a report above this report then check where that report's data range ends.
if (addressHelper.First().Equals(adLocal[0].First()))
{
addressHelper = addressHelper.Substring(1, addressHelper.Length - 1);
int addressHelperInt = Int32.Parse(addressHelper);
int addressLocalInt = Int32.Parse(adLocal[0].Substring(1, adLocal[0].Length - 1));
if (addressHelperInt > addressLocalInt)
{
activeRow = addressHelperInt + 2;
queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn],
activeSheet.Cells[activeRow, activeColumn + dataLength - 1]);
}
}
}
((Style)queryInformationRange.Style).WrapText = false;
queryInformationRange.Font.Italic = true;
queryInformationRange.MergeCells = true;
queryInformationRange.Borders.Weight = XlBorderWeight.xlThin;
queryInformationRange.Value2 = queryInformation;
if (report.Data != null)
{
dataLength = report.Data.GetLength(1);
Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn],
activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]);
dataRange.Value2 = report.Data;
// Saves the last cell in this particular data range.
string endDataRow = "";
endDataRow = adLocal[0].First() + (activeRow + infoRows + report.Data.GetLength(0)).ToString();
_addressQueries.Add(endDataRow);
Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn],
activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]);
headerRange.Value2 = report.Headers;
headerRange.Font.Bold = true;
}
}
}
private void btnSendToExcel_Click(object sender, EventArgs e)
{
Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (this.dgvReferOrderAll.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
string Date = DateTime.Now.ToString("yyyy-MM-dd");
//設定滙出後的存檔路徑(儲存在桌面)
string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) +
@"\BentoReport_" + Date + ".xls";
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = false;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
PGB pgb = new PGB();
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = dgvReferOrderAll.Rows.Count - 1;
pgb.progressBar1.Step = 1;
pgb.progressBar1.Value = 0;
pgb.progressBar1.ForeColor = Color.Pink;
pgb.progressBar1.Style = ProgressBarStyle.Continuous;
pgb.Show();
//寫入欄位名稱
for (int i = 0; i < dgvReferOrderAll.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvReferOrderAll.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvReferOrderAll.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
for (int j = 0; j < dgvReferOrderAll.Columns.Count; j++)
{
if (dgvReferOrderAll[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvReferOrderAll[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvReferOrderAll[j, i].Value.ToString();
}
}
//設定欄位靠右
excel.get_Range("A" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
xlHAlignRight;
excel.get_Range("C" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
xlHAlignRight;
//設定欄位顏色
excel.get_Range("A" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("B" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("C" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("D" + (i + 2)).Interior.Color = Color.Pink;
//設定欄位框線
excel.get_Range("A" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("B" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("C" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("D" + (i + 2)).Borders.LineStyle = 1;
}
/*
先將DataGridView的Rows總數給變數aa，以利後續透過aa+1的方式來新增要加入的資料
(將DataGridView資料轉至Excel後，在Rows下方插入TextBox.Text)
*/
int aa = dgvReferOrderAll.Rows.Count + 1;
excel.Cells[aa, 1] = "[葷]數量：";
excel.Cells[aa, 2] = txtTotalOrder0.Text;
excel.Cells[aa, 3] = "[素]數量：";
excel.Cells[aa, 4] = txtTotalOrder1.Text;
//設定欄位靠右
excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//設定欄位顏色
excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
//設定欄位字體顏色
excel.get_Range("A" + aa).Font.Color = Color.Snow;
excel.get_Range("B" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
//設定欄位字體為粗體
excel.get_Range("A" + aa).Font.Bold = true;
excel.get_Range("B" + aa).Font.Bold = true;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
aa = aa + 1;
excel.Cells[aa, 1] = "[葷]金額：";
excel.Cells[aa, 2] = txtTotalPrice0.Text;
excel.Cells[aa, 3] = "[素]金額：";
excel.Cells[aa, 4] = txtTotalPrice1.Text;
excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("A" + aa).Font.Color = Color.Snow;
excel.get_Range("B" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
excel.get_Range("A" + aa).Font.Bold = true;
excel.get_Range("B" + aa).Font.Bold = true;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
aa = aa + 1;
excel.Cells[aa, 3] = "總金額：";
excel.Cells[aa, 4] = txtTotalPriceAll.Text;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit();
//自動調整列高
excel.Cells.EntireColumn.AutoFit();
//將所有欄位做垂直置中
excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
//將A1~D1的欄位做水平置中
excel.get_Range("A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("B1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("C1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("D1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//設定A1~D1欄位框線
excel.get_Range("A1").Borders.LineStyle = 1;
excel.get_Range("B1").Borders.LineStyle = 1;
excel.get_Range("C1").Borders.LineStyle = 1;
excel.get_Range("D1").Borders.LineStyle = 1;
excel.get_Range("A1").Font.Color = Color.White;
excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
excel.get_Range("B1").Font.Color = Color.White;
excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
excel.get_Range("C1").Font.Color = Color.White;
excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
excel.get_Range("D1").Font.Color = Color.White;
excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
/*
excel.get_Range("E1").Font.Color = Color.White;
excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
excel.get_Range("F1").Font.Color = Color.White;
excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
excel.get_Range("G1").Font.Color = Color.White;
excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
excel.get_Range("H1").Font.Color = Color.White;
excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
excel.get_Range("I1").Font.Color = Color.White;
excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
excel.get_Range("J1").Font.Color = Color.White;
excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
excel.get_Range("K1").Font.Color = Color.White;
excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
excel.get_Range("L1").Font.Color = Color.White;
excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
excel.get_Range("M1").Font.Color = Color.White;
excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
excel.get_Range("N1").Font.Color = Color.White;
excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
excel.get_Range("O1").Font.Color = Color.White;
excel.get_Range("O1").Interior.Color = Color.DodgerBlue;
*/
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//將檔案儲存到SaveFile指定的位置
excel.ActiveWorkbook.SaveCopyAs(SaveFilePath);
pgb.Close();
MessageBox.Show("已成功滙出Excel檔！" + Environment.NewLine + "檔案儲存在您電腦的桌面，檔名：BentoReport_" +
Date + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//關閉工作簿和結束Excel程式
excel.Workbooks.Close();
excel.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
ex.Visible = true;
ex.SheetsInNewWorkbook = 12;
Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);
ex.DisplayAlerts = false;
var currentYear1 = DateTime.Now.Year;
for (int i = 0; i < 12; i++)
{
Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(i + 1);
sheet.StandardWidth = 3;
sheet.Name = months[i];
sheet.Cells[1, 1] = String.Format("Номер отеля | День");
if (months[i] == "01" || months[i] == "03" || months[i] == "05" || months[i] == "07" || months[i] == "08" || months[i] == "10" || months[i] == "12")
{
for (int j = 2; j < 33; j++)
{
sheet.Cells[j] = String.Format("{0}", j - 1);
}
for (int j = 2; j < 17; j++)
{
sheet.Cells[j, 1] = String.Format("{0}", j - 1);
}
}
if (months[i] == "02")
{
for (int j = 2; j < 31; j++)
{
sheet.Cells[j] = String.Format("{0}", j - 1);
}
for (int j = 2; j < 17; j++)
{
sheet.Cells[j, 1] = String.Format("{0}", j - 1);
}
}
if (months[i] == "04" || months[i] == "06" || months[i] == "09" || months[i] == "11")
{
for (int j = 2; j < 32; j++)
{
sheet.Cells[j] = String.Format("{0}", j - 1);
}
for (int j = 2; j < 17; j++)
{
sheet.Cells[j, 1] = String.Format("{0}", j - 1);
}
}
}
SQLiteConnection sql = new SQLiteConnection(@"Data Source=D:\VS\Diplom\Guest.sqlite");
sql.Open();
int count = 0;
SQLiteCommand sc4 = new SQLiteCommand("select count (*) as '" + count + "'from Гости", sql); //кол-во строк в таблице
count = Convert.ToInt32(sc4.ExecuteScalar());
int[] b = new int[count]; //получение id
int o = 0;
SQLiteCommand sc3 = new SQLiteCommand("select id from Гости", sql);
var mas1 = sc3.ExecuteReader();
while (mas1.Read())
{
b[o] = Convert.ToInt32(mas1["id"]);
o++;
}
string Date1, Date2, Number = "";
for (int j = 0; j < count; j++)
{
SQLiteCommand sc = new SQLiteCommand("select Дата_Заезда from Гости where id='" + b[j] + "'", sql);
SQLiteCommand sc1 = new SQLiteCommand("select Дата_Выезда from Гости where id='" + b[j] + "'", sql);
SQLiteCommand sc2 = new SQLiteCommand("select Номер_Комнаты from Гости where id= '" + b[j] + "'", sql);
Date1 = sc.ExecuteScalar().ToString();
Date2 = sc1.ExecuteScalar().ToString();
Number = sc2.ExecuteScalar().ToString();
var currentYear = DateTime.Now.Date;
string[] words = Date1.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries); //разделение даты
string Day = words[0]; string Month = words[1]; string Year = words[2];
words = Date2.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
string Day1 = words[0]; string Month1 = words[1]; string Year1 = words[2];
Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(Month);
if (listBox2.SelectedItem.ToString() == currentYear1.ToString())
{
if (Month == Month1)
{
((Excel.Worksheet)ex.Application.ActiveWorkbook.Sheets[Month]).Select();
Excel.Range range2 = ex.get_Range(ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day) + 1], ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day1) + 1]);
range2.Interior.Color = ColorTranslator.ToOle(Color.Blue);
}
else
{
string h = Day1;
for (int a = Convert.ToInt32(Month); a <= Convert.ToInt32(Month1); a++)
{
if (a != Convert.ToInt32(Month1))
{
if (a == 01 || a == 03 || a == 05 || a == 07 || a == 08 || a == 10 || a == 12)
{
Day1 = "31";
}
else if (a == 02)
{
Day1 = "29";
}
else if (a == 04 || a == 06 || a == 09 || a == 11)
{
Day1 = "30";
}
}
((Excel.Worksheet)ex.Application.ActiveWorkbook.Sheets[a]).Select();
Excel.Range range2 = ex.get_Range(ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day) + 1], ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day1) + 1]);
range2.Interior.Color = ColorTranslator.ToOle(Color.Blue);
Day1 = h;
}
}
}
}
sql.Close();
}
// Клик по кнопке составить расписание
private void createSchedule_Click(object sender, RoutedEventArgs e)
{
this.IsEnabled = false;
this.Cursor = Cursors.Wait;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("EXCEL не может быть запущен. Убедитесь, что у вас установлен пакет MS Office.", "Excel Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
//excel.Visible = true;
Excel.Workbook wb = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
ws.Name = "Расписание";
LessonsSchedule[] rings = context.lessonsSchedule
.OrderBy(lesson => lesson.numberLesson)
.ToArray();
int column = 2;
for (int row = 2; row < (App.days.Length) * rings.Length;)
{
foreach (LessonsSchedule ring in rings)
{
ws.Cells[column, 2] = ring.numberLesson + " пара" + "\n" + ring.beginTime + " - " + ring.endTime;
column++;
}
excel.get_Range(String.Format("A{0}:A{1}", row, column - 1), Type.Missing).Merge(Type.Missing);
ws.Cells[row, 1] = App.days[(row - 2) / rings.Length];
row += rings.Length;
}
// Список всех преподователей из таблицы правил
int[] teachers = context.regulation
.Join(
context.teacher,
reg => reg.idTeacher,
teach => teach.id,
(reg, teach) => new { reg.idTeacher, teach.lastname }
)
.Distinct()
.OrderBy(x => x.lastname)
.Select(x => x.idTeacher)
.ToArray();
//Количество недель в семестре
int weeksCount;
int lectures; // Количество часов на лекции
int practices; // Количество часов на практику
int laboratorys; // КОличествво часов на лабораторные
int maxLessons; // Максимальное число пар за день
int currentTeacherNumber = 2; // Номер преподователя, нужен для заполнения excel
string lessonType; // Тип лекции (л. пр. лаб.)
List <Regulation> deleteReg = new List <Regulation>();
List <int> tempLessons = new List <int>();
foreach (int teacherID in teachers)
{
///////////////////\\\\\\\\\\\\\\\\\\\\
System.Diagnostics.Debug.WriteLine("------------------------------------------------------------------>");
///////////////////\\\\\\\\\\\\\\\\\\\\
currentTeacherNumber++;
// Текущий преподователь
Teacher currentTeacher = context.teacher
.Where(id => id.id == teacherID)
.Single();
///////////////////\\\\\\\\\\\\\\\\\\\\
System.Diagnostics.Debug.WriteLine("Преподователь: ID = {0}, Фамилия = {1}", currentTeacher.id, currentTeacher.lastname);
///////////////////\\\\\\\\\\\\\\\\\\\\
ws.Cells[1, currentTeacherNumber] = currentTeacher.lastname;
// Все правила текущего преподователя, отсортированных по дням недели
List <Regulation> teacherFromReg = context.regulation
.Where(id => id.idTeacher == teacherID)
.OrderBy(d => d.day)
.ToList();
// Рабочий план текущего преподователя
WorkPlan[] workPlan = context.workPlan
.Where(id => id.idTeacher == teacherID)
.ToArray();
foreach (WorkPlan plan in workPlan)
{
// Количество дней в семестре
TimeSpan daysOfSemester = DateTime.Parse(plan.endDate) - DateTime.Parse(plan.beginDate);
//Количество недель в семестре
weeksCount = daysOfSemester.Days / 7;
// Количество часов на лекции в неделю у данного преподователя на данный предмет
lectures = plan.lecturesTime / weeksCount;
// Количество часов на практику в неделю у данного преподователя на данный предмет
practices = plan.practiceTime / weeksCount;
// Количество часов на лабораторные в неделю у данного преподователя на данный предмет
laboratorys = plan.laboratoryTime / weeksCount;
///////////////////\\\\\\\\\\\\\\\\\\\\
System.Diagnostics.Debug.WriteLine("Количество недель в семестре {0}", weeksCount);
System.Diagnostics.Debug.WriteLine("Рабочий план НА СЕМЕСТР: ID = {0}, Лекций = {1}, Практик = {2}, Лаб = {3}", plan.id, plan.lecturesTime, plan.practiceTime, plan.laboratoryTime);
System.Diagnostics.Debug.WriteLine("Рабочий план НА НЕДЕЛЮ: ID = {0}, Лекций = {1}, Практик = {2}, Лаб = {3}", plan.id, lectures, practices, laboratorys);
///////////////////\\\\\\\\\\\\\\\\\\\\
// Удаляем использованные правила
foreach (Regulation del in deleteReg)
{
teacherFromReg.Remove(del);
}
deleteReg.Clear();
foreach (Regulation reg in teacherFromReg)
{
maxLessons = reg.maxLesson;
///////////////////\\\\\\\\\\\\\\\\\\\\
System.Diagnostics.Debug.WriteLine("");
System.Diagnostics.Debug.WriteLine("Правило: ID = {0}, День недели = {1}, Занятия = {2}, Макс. занятий = {3}", reg.id, App.days[reg.day], reg.lessons, reg.maxLesson);
///////////////////\\\\\\\\\\\\\\\\\\\\
List <int> lessons = reg.lessons.Split(',')
.Select(lesson => int.Parse(lesson))
.ToList();
tempLessons.Clear();
foreach (int lesson in lessons)
{
// Условие выхода из цикла
if (maxLessons == 0)
{
// Добавляем запись правила в список, для удаления на следующей итерации
deleteReg.Add(reg);
break;
}
// Уменьшаем количество часов на занятия
if (lectures > 0)
{
lectures--; lessonType = " л.";
}
else if (practices > 0)
{
practices--; lessonType = " пр.";
}
else if (laboratorys > 0)
{
laboratorys--; lessonType = " лаб.";
}
else
{
break;
}
maxLessons--;
tempLessons.Add(lesson);
// Возвращаем название предмета и его id
var subject = context.workPlan
.Where(currentPlan => currentPlan.id == plan.id)
.Join(
context.subject,
currentPlan => currentPlan.idSubject,
sub => sub.id,
(currentPlan, sub) => new { sub.subjectName, sub.id }
)
.Single();
// По id предмета возвращаем все аудитории, в которых он преподаётся
string[] classes = context.subjectClasses
.Join(
context.classroom,
sc => new { k1 = sc.idSubject, k2 = sc.idClassroom },
cl => new { k1 = subject.id, k2 = cl.id },
(sc, cl) => cl.className
)
.ToArray();
ws.Cells[reg.day * rings.Length + 1 + lesson, currentTeacherNumber] = subject.subjectName + lessonType + "\n" + classes[new Random().Next(classes.Length)];
////////////////lessons.Remove(lessons[lesson]);
}
///////////////////\\\\\\\\\\\\\\\\\\\\
System.Diagnostics.Debug.WriteLine("Остаток НА НЕДЕЛЮ: Лекций: = {0}, Практических = {1}, Лабораторных = {2}", lectures, practices, laboratorys);
///////////////////\\\\\\\\\\\\\\\\\\\\
System.Diagnostics.Debug.WriteLine("Расписание: предмет = {0}, День недели = {1}, Занятия = {2}, Осталось свободных занятий = {3}",
context.subject.Where(id => id.id == plan.idSubject).Select(n => n.subjectName).Single(),
App.days[reg.day],
String.Join(",", tempLessons
.Select(x => x)
.OrderBy(x => x)
.ToArray()),
maxLessons);
///////////////////\\\\\\\\\\\\\\\\\\\\
}
}
}
/// ------- Цикл по составлению рассписания кончился ------------
/// Стили для excel
// Преподаватели
var teachersRange = ws.Range["C1", String.Format("{0}1", Convert.ToChar(67 + teachers.Length))]; // где 67 - код из ASCII буквы C
teachersRange.Orientation = 90;
teachersRange.WrapText = false;
teachersRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
teachersRange.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
teachersRange.Font.Size = 16;
teachersRange.Font.Name = "Arial";
// Дни недели
var daysRange = ws.Range["A2", String.Format("A{0}", rings.Length * App.days.Length)];
daysRange.Orientation = 90;
daysRange.WrapText = false;
daysRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
daysRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
daysRange.Font.Size = 16;
daysRange.Font.Name = "Arial";
// Номера занятий
var lessonRange = ws.Range["B2", String.Format("B{0}", rings.Length * App.days.Length + 1)];
lessonRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
lessonRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
lessonRange.Font.Size = 12;
lessonRange.ColumnWidth = 15;
// Расписание
var scheduleRange = ws.Range["C2", String.Format("{0}{1}", Convert.ToChar(67 + teachers.Length), rings.Length * App.days.Length + 1)];
scheduleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
scheduleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
scheduleRange.Font.Size = 12;
scheduleRange.WrapText = true;
scheduleRange.Rows.AutoFit();
scheduleRange.ColumnWidth = 25;
SaveFileDialog saveExcelFile = new SaveFileDialog();
saveExcelFile.FileName = "Расписание";
saveExcelFile.DefaultExt = ".xlsx";
saveExcelFile.Filter = "Книга Excel 2007 (*.xlsx)|*.xlsx|Книга Excel 2003 (*.xls)|*.xls";
// Сохранение файла и закрытие excel
if ((bool)saveExcelFile.ShowDialog())
{
wb.SaveAs(saveExcelFile.FileName);
wb.Close(true);
excel.Quit();
excel.DisplayAlerts = false;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
Process.Start(saveExcelFile.FileName);
}
this.IsEnabled = true;
this.Cursor = null;
}
//生成Excel
private bool ExportToExcel(DataTable dt)
{
bool result = false;
if (dt.Rows.Count > 65536)
{
MessageBox.Show("數據記錄太多(最多不能超過65536條)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return result;
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
labMsg.Text = "Excel正在匯出中…………";
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机器未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(true);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "過膠機品質自檢日報表";
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印
worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印
worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效
worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁
worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中
worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102);
worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126);
worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126);
xlApp.Visible = false;
#region 列名
int RowIndex = 2;
MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 32], "過膠機品質自檢日報表", 16, 65535, 0); //大標題
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 32], "當班品檢情況", 12, 65535, 0);
RowIndex = RowIndex + 1;
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "累計數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "車速", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "溫度", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "壓力", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 23], xlApp.Cells[RowIndex, 23], "當班品檢總次數", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "第( )次品檢", 12, 65535, 11);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "打卡數量", 12, 65535, 11);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "抽檢數量", 12, 65535, 9.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "品檢問題", 12, 65535, 18);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "次品數量", 12, 65535, 11);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "廢品數量", 12, 65535, 9.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "問題處理辦法", 12, 65535, 24);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 31], xlApp.Cells[RowIndex, 31], "品檢開始時間", 12, 65535, 24);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 32], xlApp.Cells[RowIndex, 32], "品檢結束時間", 12, 65535, 24);
#endregion
#region 填充數據 向Excel中逐行逐列写入表格中的数据
RowIndex = RowIndex + 1; //數據開始的行號
int ExcelCurrectRow = RowIndex; //當前行
int dtRowNO;
int realRowNO = 0;//dt實際行
int dtKeyRowNO = RowIndex;//NO:4
int megreCount = 0;
int dtProblemRowNO = 24;//物料編號所在列
int tempRow;
int tempCol;
DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" });
for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++)
{
DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' ");
megreCount = currectKeyRow.Length;
for (tempRow = 0; tempRow < megreCount; tempRow++)
{
if (tempRow == 0)
{
for (tempCol = 1; tempCol < dtProblemRowNO; tempCol++) //先合併物料編號之前的單元格
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true);
}
realRowNO += megreCount;
}
for (tempCol = dtProblemRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false);
}
dtKeyRowNO++;
}
}
#endregion
#region 設置Sheet邊框
//range:
Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]);
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
#endregion
Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]);
DataFormat.Font.Name = "楷体";
DataFormat.Font.Size = 10;
Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]);
IntFormat.NumberFormatLocal = "0.00";
#region savecode
Object missing = Missing.Value;
SaveFileDialog saveForm = new SaveFileDialog();
saveForm.Filter = "Execl files (*.xls)|*.xls";
saveForm.FilterIndex = 0;
saveForm.FileName = "過膠機品質自檢日報表";
if (saveForm.ShowDialog() == DialogResult.OK)
{
string fileName = saveForm.FileName;
workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
#endregion
result = true;
}
catch (Exception ex)
{
throw;
}
finally
{
labMsg.Text = "";
xlApp.Quit();
IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
System.GC.Collect();
}
labMsg.Text = "";
return result;
}
public ActionResult DownLoadFile()
{
try
{
#region Exal
byte[] bytes;
string fileName = "", contentType = "", PaperPath = "";
DataSet dswordchargeinrow = new DataSet();
KeystoneProject.Buisness_Logic.Master.Bl_Services Bl_obj = new Buisness_Logic.Master.Bl_Services();
Services AddServiceMod = new Services();
AddServiceMod.StoreAllServices = Bl_obj.GetAllServicesExcelData();
AddServiceMod.dsServicesCharges = Bl_obj.GetAllWardName();
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Excel.Range chartRange;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
app.get_Range("A2", "S2").Locked = true;
string WardNAme;
//worksheet.Name = "Exported from Keystone";
int count = 0;
for (int i = 1; i < AddServiceMod.StoreAllServices.Tables[0].Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = AddServiceMod.StoreAllServices.Tables[0].Columns[i - 1].ColumnName;
count = i;
}
int m = 0;
for (int word = 0; word < AddServiceMod.dsServicesCharges.Tables[0].Rows.Count; word++)
{
worksheet.Cells[1, count + word] = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"];
// worksheet.get_Range(worksheet.Cells[1, word], worksheet.Cells[4, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
WardNAme = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"].ToString() + ",";
m++;
}
for (int i = 0; i < AddServiceMod.StoreAllServices.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < AddServiceMod.StoreAllServices.Tables[0].Columns.Count; j++)
{
// if (j < 2)
// {
worksheet.Cells[i + 2, j + 1] = AddServiceMod.StoreAllServices.Tables[0].Rows[i][j].ToString();
// }
if (AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"].ToString() != "")
{
dswordchargeinrow = Bl_obj.SelectServiceChargesbyID(Convert.ToInt16(AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"]));
if (dswordchargeinrow.Tables[0].Rows.Count > 0)
{
foreach (DataRow drcharge in AddServiceMod.dsServicesCharges.Tables[0].Rows)
{
int charge = 0;
foreach (DataRow dr in dswordchargeinrow.Tables[0].Rows)
{
string WardName = drcharge["WardName"].ToString();
string WardName1 = dr["WardName"].ToString();
if (drcharge["WardName"].ToString() == dr["WardName"].ToString())
{
worksheet.Cells[i + 2, charge + 7] = dr["GeneralCharges"];
}
charge++;
}
}
}
}
}
}
#endregion
Random obj = new Random();
int a = obj.Next(1, 300);
string DateCode = "Service_Excel_Data" + Bl_obj.ReffrenceExcel();
fileName = Server.MapPath("~/") + "Service_Excel/" + DateCode;
workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
string Path = fileName + ".xlsx";
string filename = System.IO.Path.GetFileName(DateCode + ".xlsx");
// string fullpath = System.IO.Path.Combine(Path, fileName);
contentType = MimeMapping.GetMimeMapping(Path);
File(Path, contentType, filename);
// File(Path, contentType, filename);
return(new JsonResult {
Data = "Done", JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
//fileName = "Service_Excel_Data" + Bl_obj.ReffrenceCode()+".xlsx";
}
catch (Exception ex)
{
throw;
}
// return File(FileById.FileContent, "/MRDFiles/pdf", FileById.FileName);
}