private void ExcelB_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelApp.get_Range("A1").Value = "Id"; ExcelApp.get_Range("B1").Value = "Login"; ExcelApp.get_Range("C1").Value = "Password"; ExcelApp.get_Range("E1").Value = "Данные за: " + DateTime.Now.ToShortDateString(); for (int i = 1; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1.Rows[i].Cells[j].GetType().Name == "DataGridViewComboBoxCell") { DataGridViewComboBoxCell DataC = new DataGridViewComboBoxCell(); DataC = (DataGridViewComboBoxCell)dataGridView1.Rows[i].Cells[j]; ExcelApp.Cells[i + 1, j + 1] = DataC.EditedFormattedValue; } else { ExcelApp.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value; } } } ExcelApp.Visible = true; ExcelApp.UserControl = true; }
private void SetExcelFormat(Microsoft.Office.Interop.Excel.Application excel, ref int row, IGrouping <string, Model.Product> item) { excel.Cells[row, 1] = item.Key; excel.get_Range(excel.Cells[row, 1], excel.Cells[row, 9]).Interior.Color = global::Helper.ExcelColor.LightPink; excel.get_Range(excel.Cells[row, 4], excel.Cells[row, 4]).Formula = string.Format("=SUM(D{0}:C{1})", row + 1, row + item.Count()); row++; foreach (var pro in item) { excel.Cells[row, 1] = pro.DepotName; excel.Cells[row, 2] = pro.Id; excel.Cells[row, 3] = pro.ProductName; excel.Cells[row, 4] = pro.StocksQuantity; excel.Cells[row, 5] = pro.CnName; excel.Cells[row, 6] = pro.HandbookId; excel.Cells[row, 7] = pro.HandbookProductId; excel.Cells[row, 8] = pro.CustomerInvoiceXOId; excel.Cells[row, 9] = (pro.NetWeight.Value / 1000).ToString("0.#####"); int col = 10; foreach (var dic in pro.MaterialDic) { excel.Cells[row, col++] = dic.Value; } row++; } //每组数据之间不留空行 //row++; }
private void SetExcelFormat(Microsoft.Office.Interop.Excel.Application excel, ref int row, IGrouping <string, Model.Product> item) { excel.Cells[row, 1] = item.Key; excel.get_Range(excel.Cells[row, 1], excel.Cells[row, 1]).Interior.Color = "255"; excel.get_Range(excel.Cells[row, 3], excel.Cells[row, 3]).Formula = string.Format("=SUM(C{0}:C{1})", row + 1, row + item.Count()); row++; foreach (var pro in item) { excel.Cells[row, 1] = pro.Id; excel.Cells[row, 2] = pro.ProductName; excel.Cells[row, 3] = pro.StocksQuantity; excel.Cells[row, 4] = pro.CnName; excel.Cells[row, 5] = (pro.NetWeight.Value / 1000).ToString("0.#####"); int col = 6; foreach (var dic in pro.MaterialDic) { excel.Cells[row, col++] = dic.Value; } row++; } //每组数据之间不留空行 //row++; }
public void ExportarExcel(DataGridView grade, string texto) { //exportar dados para o excel try { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Title = "Salvar Como Arquivo Excel"; saveFileDialog.FileName = "Relatório"; saveFileDialog.Filter = "Excel 2007|*.xlsx|Excel 2003|*.xls"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Cells[1, 2] = texto; for (int coluna = 1; coluna < grade.Columns.Count + 1; coluna++) { ExcelApp.Cells[3, coluna] = grade.Columns[coluna - 1].HeaderText; } for (int linha = 0; linha < grade.Rows.Count; linha++) { for (int coluna = 0; coluna < grade.Columns.Count; coluna++) { ExcelApp.Cells[linha + 4, coluna + 1] = grade.Rows[linha].Cells[coluna].Value.ToString(); } } ExcelApp.Columns.AutoFit(); Microsoft.Office.Interop.Excel.Range celulas; //Aqui para baixo configuro o texto de layout celulas = ExcelApp.get_Range("A1", "E1"); celulas.MergeCells = true; celulas.Font.Italic = true; celulas.Font.Size = 20F; celulas = ExcelApp.get_Range("A1", "F1"); //Recupero o conjunto de células de A1 até Z1 celulas.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; //centralizo as células celulas.Font.Bold = true; //mudo a fonte para negrito //Aqui para baixo configuro o nome da coluna celulas = ExcelApp.get_Range("A3", "Z3"); celulas.Font.Size = 12F; celulas.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; //centralizo as células celulas.Font.Bold = true; //mudo a fonte para negrito //celulas.Interior.Color = ColorTranslator.ToWin32(Color.LightGray); //mudo as cores das celulas //celulas.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //insiro a borda nas celulas ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog.FileName.ToString()); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); MessageBox.Show("Arquivo exportado com sucesso!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show("Inconsitência: " + ex.Message, "Possível falha", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
private void SetExcelFormat(Microsoft.Office.Interop.Excel.Application excel, ref int col, ref int row, IGrouping <string, Book.Model.AssemblySiteInventoryDetail> item) { excel.Cells[row, 1] = item.Key; excel.get_Range(excel.Cells[row, 1], excel.Cells[row, 5 + 1 + this._assemblySiteInventory.Details[0].Product.MaterialDic.Keys.Count]).Interior.Color = "255"; //红色 excel.get_Range(excel.Cells[row, 5], excel.Cells[row, 5]).Formula = string.Format("=SUM(E{0}:E{1})", row + 1, row + item.Count()); col = 7; foreach (var ec in this._assemblySiteInventory.Details[0].Product.MaterialDic) { string excelColumnName = CountExcelColumnName(col); excel.get_Range(excel.Cells[row, col], excel.Cells[row, col]).Formula = string.Format("=SUM({2}{0}:{2}{1})", row + 1, row + item.Count(), excelColumnName); col++; } row++; }
private void button12_Click(object sender, EventArgs e) { // 엑셀 파일로 저장 Microsoft.Office.Interop.Excel.Application xcelApp = new Microsoft.Office.Interop.Excel.Application(); xcelApp.Application.Workbooks.Add(Type.Missing); // 엑셀 서식 지정 string rangStr = "A2:A" + (dataGridView1.Rows.Count + 1); MessageBox.Show(rangStr); Microsoft.Office.Interop.Excel.Range range = xcelApp.get_Range(rangStr, Type.Missing); range.NumberFormat = "0"; // Head Text 설정 for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { xcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; } // 데이터 설정 for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { xcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } xcelApp.Columns.AutoFit(); xcelApp.Visible = true; }
static void TestCOM() { //<Snippet3> // In C# 3.0 and earlier versions, you need to supply an argument for // every parameter. The following call specifies a value for the first // parameter, and sends a placeholder value for the other six. The // default values are used for those parameters. var excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Workbooks.Add(); excelApp.Visible = true; var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1; excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //</Snippet3> //<Snippet13> // The following code shows the same call to AutoFormat in C# 4.0. Only // the argument for which you want to provide a specific value is listed. excelApp.Range["A1", "B4"].AutoFormat(Format: myFormat); //</Snippet13> }
//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 = 30; excel.Cells[1, 1] = "INFORME DE VENTAS POR ORIGEN DE ORDEN"; excel.Cells[2, 1] = "DESDE " + sFechaInicio + " A " + sFechaFin; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; //excel.Cells[1, iIndiceColumna] = col.Name; } int iIndiceFila = 3; excel.Cells[6, 2].Interior.Color = Color.Aquamarine; excel.Cells[8, 1].Interior.Color = Color.Yellow; excel.Cells[8, 2].Interior.Color = Color.Yellow; excel.Cells[8, 3].Interior.Color = Color.Yellow; excel.get_Range("B6", "B6").BorderAround(); excel.get_Range("A8", "C8").BorderAround(); 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.Visible = true; }
/// <summary> /// 创建一个新的Excel文件,并写入数据集 /// <para>请注意,Excel文件可能创建失败</para> /// <para>原因:指定Excel文件已存在</para> /// </summary> /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param> /// <param name="path">请提供一个Excel文件路径,如果不存在则自动创建</param> /// <returns></returns> public static bool CreateExcel(DataSet ds, string path) { bool result = true; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//create Excel manipulate objects try { excel.SheetsInNewWorkbook = ds.Tables.Count; //获取Sheet档数量 excel.Workbooks.Add(); for (int number = 0; number < ds.Tables.Count; number++) //循环添加Sheet档 { Microsoft.Office.Interop.Excel.Worksheet sheet = excel.ActiveWorkbook.Worksheets[number + 1] as Microsoft.Office.Interop.Excel.Worksheet; //获取sheet; DataTable dt = ds.Tables[number]; //获取表 sheet.Name = dt.TableName; //设置Sheet档名 int i = 0; for (; i < dt.Columns.Count; i++) //循环添加列头 { sheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; //设置列头名 } Microsoft.Office.Interop.Excel.Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, i]); //设置编辑区域 range.Font.Bold = true; //字体加粗 range.Font.ColorIndex = 0; //字体颜色 range.Interior.ColorIndex = 15; //背景颜色 range.ColumnWidth = 15; //列宽 range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //边框样式 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //字体居中 for (int y = 0; y < dt.Rows.Count; y++) //循环添加行 { for (int x = 0; x < dt.Rows[y].Table.Columns.Count; x++) //循环添加列 { sheet.Cells[y + 2, x + 1] = dt.Rows[y][x]; //设置列值 } } } excel.Visible = false;//设置为预览 //System.Threading.Thread.Sleep(5000);//线程延迟5秒再预览 excel.ActiveWorkbook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);//另存为 } catch (Exception ex) { ex.Message.ToString(); result = false; } finally { excel.ActiveWorkbook.Close(); //关闭Excel对象 excel.Quit(); //退出 KillEXCELProcess(); } return(result); }
/// <summary> /// DataSet导出到Excel /// </summary> public static void DataSetToExcel(DataSet dataSet, String savePath) { DataTable dataTable = dataSet.Tables[0]; //默认只导出第一个table int rowNumber = dataTable.Rows.Count; //不包括字段名 int columnNumber = dataTable.Columns.Count; int colIndex = 0; //建立Excel对象 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Application.Workbooks.Add(true); //这样就只会打开一个sheet,不会出现下面说到的打开两个sheet:一个空白,一个有数据的情况 Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.ActiveWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet; Microsoft.Office.Interop.Excel.Range range; try { //生成字段名称 foreach (DataColumn col in dataTable.Columns) { colIndex++; excelApp.Cells[1, colIndex] = col.ColumnName; } object[,] objData = new object[rowNumber, columnNumber]; for (int r = 0; r < rowNumber; r++) { for (int c = 0; c < columnNumber; c++) { objData[r, c] = dataTable.Rows[r][c]; } Application.DoEvents(); } // 写入Excel range = excelApp.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[rowNumber + 1, columnNumber]); range.NumberFormat = "@"; //设置单元格为文本格式 range.Value2 = objData; //excelApp.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm"; workbook.SaveAs(savePath); MessageBox.Show("导出成功!"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { workbook.Close(); workbook = null; excelApp.Quit(); KillProcess(excelApp); } }
private void button3_Click(object sender, EventArgs e) { // Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; // Microsoft.Office.Interop.Excel.Workbook xlWorkBook; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Range chartRange; if (kdvcustomer.Rows.Count > 0) { Microsoft.Office.Interop.Excel.Application XcelApp = new Microsoft.Office.Interop.Excel.Application(); XcelApp = new Microsoft.Office.Interop.Excel.Application(); //xlWorkBook = XcelApp.Workbooks.Add (misValue); // xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item (1); XcelApp.Application.Workbooks.Add(Type.Missing); for (int i = 1; i < kdvcustomer.Columns.Count + 1; i++) { XcelApp.Cells [1, i] = kdvcustomer.Columns [i - 1].HeaderText; } for (int i = 0; i < kdvcustomer.Rows.Count; i++) { for (int j = 0; j < kdvcustomer.Columns.Count; j++) { XcelApp.Cells [i + 2, j + 1] = kdvcustomer.Rows [i].Cells [j].Value.ToString(); } } XcelApp.Columns.AutoFit(); chartRange = XcelApp.get_Range("A1", "P1"); chartRange.Font.Bold = true; XcelApp.Visible = true; //chartRange = XcelApp.get_Range ("H1"); //chartRange.FormulaR1C1 = "RECEIVED EQUIPMENT LIST"; chartRange.HorizontalAlignment = 3; chartRange.VerticalAlignment = 3; chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.CadetBlue); chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Crimson); chartRange.Font.Size = 15; } }
public static void XlsUtil(DataTable table) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; for (int i = 0; i < table.Columns.Count; i++) { excel.Cells[1, i + 1] = table.Columns[i].Caption; } if (table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Columns.Count; j++) { if (table.Columns[j].ColumnName == "正味價格" || table.Columns[j].ColumnName == "納期") { string str = table.Rows[i][j].ToString(); excel.Cells[i + 2, j + 1] = str; } else { string str = "'" + table.Rows[i][j].ToString(); excel.Cells[i + 2, j + 1] = str; } } } } Microsoft.Office.Interop.Excel.Range oRange = excel.get_Range("A1", "A1"); oRange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Range iRange = excel.get_Range("A1", "A1"); iRange.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; }
public static void ExportDatagridview(DataGridView dgv) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible == true) { excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText; } } if (dgv.Rows.Count > 0) { for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { if (dgv.Columns[j].Visible != false) { string str = "'" + dgv.Rows[i].Cells[j].Value.ToString(); excel.Cells[i + 2, j + 1] = str; } } } } Microsoft.Office.Interop.Excel.Range oRange = excel.get_Range("A1", "A1"); oRange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Range iRange = excel.get_Range("A1", "A1"); iRange.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; }
//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 = 30; excel.Cells[1, 1] = "INFORME FLUJO DIARIO DE CLIENTES"; excel.Cells[2, 1] = "DESDE " + sFechaInicio + " A " + sFechaFin; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; if (iIndiceColumna != 0) { excel.Columns.ColumnWidth = 15; } excel.Cells[4, iIndiceColumna] = col.HeaderText; excel.Cells[4, iIndiceColumna].Interior.Color = Color.Yellow; excel.Cells[4, iIndiceColumna].BorderAround(); } int iIndiceFila = 4; 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("A4", "G4").BorderAround(); excel.Visible = true; }
// Prints all the unique, recorded, MAC-addresses in the textfield. private void PrintMacAddresses_Click(object sender, EventArgs e) { StringBuilder macAddresses = new StringBuilder(); dataTextBox.Clear(); // Start Excel Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; // Get path to excecutable (Same place as where the Excel Sheet is) string exeDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); // Open workbook Microsoft.Office.Interop.Excel._Workbook oWB = oXL.Workbooks.Open(System.IO.Path.Combine(exeDir, "MAC adress list example.xlsx")); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; int rowIdx; int notEmpty; foreach (string macAddress in uniqueMacAddresses) { rowIdx = 1; notEmpty = 1; macAddresses.Append(macAddress + "\n"); // Loops through the rows in the Excel-document until you find // a row that is completely empty. while (notEmpty > 0) { string aCellAddress = "B" + rowIdx.ToString(); Microsoft.Office.Interop.Excel.Range row = oXL.get_Range(aCellAddress, aCellAddress).EntireRow; notEmpty = (int)oXL.WorksheetFunction.CountA(row); rowIdx++; } // Set the first found completely empty row to a MAC-address value oSheet.Cells[rowIdx - 1, 2] = macAddress; } dataTextBox.Text = macAddresses.ToString(); // Clears all the addresses so that they are removed when we are printing the next time. uniqueMacAddresses.Clear(); }
//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 PRODUCTOS"; 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; }
static void SplitAndMail(string FileName) { Microsoft.Office.Interop.Excel.Application xlApp; xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; FileInfo fi = new FileInfo(FileName); string FullFileName = fi.FullName.ToString(); try { xlWorkBook = xlApp.Workbooks.Open(FullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); } catch { Console.WriteLine("You must choose a file"); return; } xlWorkBook = xlApp.Workbooks.Open(FullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Microsoft.Office.Interop.Excel.Range rng = xlApp.get_Range("A1"); //int index = 0; string liteFileName = FileName.Replace(".xlsx", ""); foreach (Microsoft.Office.Interop.Excel.Worksheet displayWorksheet in xlApp.Worksheets) { string root = fi.Directory.ToString(); string sheetName = displayWorksheet.Name.ToString(); string SaveFileName = root + "\\" + liteFileName + "." + sheetName + ".xlsx"; //Microsoft.Office.Interop.Excel.Application NewxlApp; //NewxlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook NewWorkbook; NewWorkbook = xlApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet NewWorkSheet; NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkbook.Worksheets.Add(); displayWorksheet.Copy(NewWorkSheet); NewWorkbook.SaveAs(SaveFileName); NewWorkbook.Save(); NewWorkbook.Close(); } }
/// <summary> /// 读取指定单元格数据 /// </summary> /// <param name="row">行序号</param> /// <param name="column">列序号</param> /// <returns>该格的数据</returns> public string ReadData(string fileName, string sheetName, int row, int column) { Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkBook; myExcel = new Microsoft.Office.Interop.Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); Microsoft.Office.Interop.Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]); string str = range.Text.ToString(); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); return(str); }
//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] = "PRODUCTOS"; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; excel.Cells[3, iIndiceColumna] = col.HeaderText; excel.Cells[3, iIndiceColumna].Interior.Color = Color.Yellow; } int iIndiceFila = 3; 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("A3", "E3").BorderAround(); excel.Visible = true; }
private void buttonExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet; //Книга. ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelApp.get_Range("A1").Value = "Id"; ExcelApp.get_Range("B1").Value = "login"; ExcelApp.get_Range("C1").Value = "password"; ExcelApp.get_Range("D1").Value = "fullname"; ExcelApp.get_Range("E1").Value = "phone"; ExcelApp.get_Range("F1").Value = "Company"; ExcelApp.get_Range("G1").Value = "Дата создания документа: " + DateTime.Now.ToShortDateString(); for (int i = 1; i < usersDataGridView.Rows.Count; i++) { for (int j = 0; j < usersDataGridView.ColumnCount; j++) { if (usersDataGridView.Rows[i].Cells[j].GetType().Name == "DataGridViewComboBoxCell") { DataGridViewComboBoxCell dgvcbc = new DataGridViewComboBoxCell(); dgvcbc = (DataGridViewComboBoxCell)usersDataGridView.Rows[i].Cells[j]; ExcelApp.Cells[i + 1, j + 1] = dgvcbc.EditedFormattedValue; } else { ExcelApp.Cells[i + 1, j + 1] = usersDataGridView.Rows[i].Cells[j].Value; } } } //Вызываем нашу созданную эксельку. ExcelApp.Visible = true; ExcelApp.UserControl = true; }
private void ExportExcel(DataGridView dgv) { try { if (dgv.Rows.Count >= 1) { labelProgMon.Text = "开始导出"; labelProgMon.Update(); string yr_str = comboBoxYear1.Text.Trim(); string mon_str = comboBoxMon1.Text.Trim(); string day_str = comboBoxDay1.Text.Trim(); string fName = ""; SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.InitialDirectory = "d:"; saveFileDialog.Filter = "EXCEL文件|*.xlsx"; saveFileDialog.FilterIndex = 2; if (day_str == "") { saveFileDialog.FileName = yr_str + "年" + mon_str + "月清运垃圾明细表"; } else { saveFileDialog.FileName = yr_str + "年" + mon_str + "月" + day_str + "日清运垃圾明细表"; } saveFileDialog.RestoreDirectory = true; if (saveFileDialog.ShowDialog() == DialogResult.OK) { fName = saveFileDialog.FileName; progressBarMon.Value = 12; progressBarMon.Update(); this.Refresh(); //建立Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;// new Microsoft.Office.Interop.Excel.Workbooks(); Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook Microsoft.Office.Interop.Excel.Worksheet ws; //MessageBox.Show("355", "process", MessageBoxButtons.OK, MessageBoxIcon.None); int num_sheet = dgv.Rows.Count / 55; labelProgMon.Text = "完成 " + progressBarMon.Value.ToString() + "%"; labelProgMon.Update(); //MessageBox.Show(num_sheet.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.None); for (int cur_sheet = 1; cur_sheet <= num_sheet; cur_sheet++) { if (num_sheet != 1) { progressBarMon.Value = Convert.ToInt32(12 + cur_sheet * 87 / num_sheet); progressBarMon.Update(); //label5.Text = "完成 " + progressBar1.Value.ToString() + "%"; //label5.Update(); } else { progressBarMon.Value = 50; progressBarMon.Update(); //label5.Text = "完成 " + progressBar1.Value.ToString() + "%"; //label5.Update(); } if (cur_sheet == 1) ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"]; else ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet; ws.Name = cur_sheet.ToString(); //生成字段名称 excel.Cells[1, 1] = " 日期\n名称"; excel.Cells[1, 2] = "1"; excel.Cells[1, 3] = "2"; excel.Cells[1, 4] = "3"; excel.Cells[1, 5] = "4"; excel.Cells[1, 6] = "5"; excel.Cells[1, 7] = "6"; excel.Cells[1, 8] = "7"; excel.Cells[1, 9] = "8"; excel.Cells[1, 10] = "9"; excel.Cells[1, 11] = "10"; excel.Cells[1, 12] = "11"; excel.Cells[1, 13] = "12"; excel.Cells[1, 14] = "13"; excel.Cells[1, 15] = "14"; excel.Cells[1, 16] = "15"; excel.Cells[1, 17] = "合计"; excel.Cells[1, 18] = ""; excel.Cells[2, 1] = ""; excel.Cells[2, 2] = "箱数"; excel.Cells[2, 3] = "吨数"; excel.Cells[2, 4] = "吨数"; excel.Cells[2, 5] = "吨数"; excel.Cells[2, 6] = "吨数"; excel.Cells[2, 7] = "吨数"; excel.Cells[2, 8] = "吨数"; excel.Cells[2, 9] = "吨数"; excel.Cells[2, 10] = "吨数"; excel.Cells[2, 11] = "吨数"; excel.Cells[2, 12] = "吨数"; excel.Cells[2, 13] = "吨数"; excel.Cells[2, 14] = "吨数"; excel.Cells[2, 15] = "吨数"; excel.Cells[2, 16] = "吨数"; excel.Cells[2, 17] = "吨数"; excel.Cells[2, 18] = "总箱数"; Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[1, 17], excel.Cells[1, 18]); merge_range.Merge(Type.Missing); Microsoft.Office.Interop.Excel.Range merge_range2 = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]); merge_range2.Merge(Type.Missing); //填充数据 for (int i = (cur_sheet - 1) * 56 + 1; i < (cur_sheet - 1) * 56 + 57; i++) { for (int j = 0; j < dgv.ColumnCount - 1; j++) { if (dgv[j, i - 1].Value != null) { excel.Cells[i - (cur_sheet - 1) * 56 + 2, j + 1] = dgv[j, i - 1].Value; } else excel.Cells[i - (cur_sheet - 1) * 56 + 2, j + 1] = ""; } } Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[58, 18]);//现有的 all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; Microsoft.Office.Interop.Excel.Range c11_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]); c11_range.HorizontalAlignment = XlHAlign.xlHAlignLeft; all_range.EntireColumn.AutoFit(); //自动调整列宽 all_range.EntireRow.AutoFit(); all_range.Borders.LineStyle = 1; labelProgMon.Text = "完成 " + progressBarMon.Value.ToString() + "%"; labelProgMon.Update(); }//end for每个sheet progressBarMon.Value = progressBarMon.Maximum; progressBarMon.Update(); wb.Saved = true; wb.SaveCopyAs(fName); //保存 excel.Quit(); //关闭进程 labelProgMon.Text = "已完成"; labelProgMon.Update(); MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None); //label5.Text = "完成"; toolStripButtonMonExl.Enabled = false; } else { progressBarMon.Visible = false; progressBarMon.Update(); labelProgMon.Text = ""; labelProgMon.Update(); MessageBox.Show("未导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None); } } } catch (Exception ex) { progressBarMon.Visible = false; progressBarMon.Update(); labelProgMon.Text = ""; labelProgMon.Update(); MessageBox.Show(ex.Message.ToString()); } //*/ }
//Función para exportar a excel private void exportarAExcel(DataGridView dgvCierre) { try { Cursor = Cursors.AppStarting; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int iIndiceColumna = 0; excel.Columns.ColumnWidth = 13; excel.Cells[1, 1] = "EMPRESA: "; excel.Cells[1, 2] = cmbEmpresa.Text; excel.Cells[2, 1] = "REPORTE:"; excel.Cells[2, 2] = "CONSULTA DE EXISTENCIAS A UNA FECHA DE CORTE:"; excel.Cells[3, 1] = "LOCALIDAD:"; excel.Cells[3, 2] = cmbOficina.Text; excel.Cells[3, 3] = "BODEGA:"; excel.Cells[3, 4] = cmbBodega.Text; excel.Cells[5, 1] = "FECHA CORTE"; excel.Cells[5, 2] = txtFechaCorte.Text; excel.Cells[6, 1] = "FECHA INICIO"; excel.Cells[6, 2] = ""; excel.Cells[7, 1] = "FECHA FIN"; excel.Cells[7, 2] = ""; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; if (iIndiceColumna == 2) { excel.Cells[9, iIndiceColumna].ColumnWidth = 20; } excel.Cells[9, iIndiceColumna] = col.HeaderText; excel.Cells[9, iIndiceColumna].Interior.Color = Color.Gray; excel.Cells[9, iIndiceColumna].BorderAround(); } int iIndiceFila = 9; 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.get_Range("A9", "E9").BorderAround(); excel.Visible = true; Cursor = Cursors.Arrow; } catch (Exception ex) { catchMensaje.lblMensaje.Text = ex.Message; catchMensaje.ShowDialog(); } }
//Función para exportar a excel private void exportarAExcel(DataGridView dgvCierre) { try { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int iIndiceColumna = 0; excel.Columns.ColumnWidth = 40; excel.Cells[1, 4] = "INFORME DE INGRESOS "; excel.Cells[2, 4] = "DESDE " + sFechaInicio + " A " + sFechaFin; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; if (iIndiceColumna == 1) { excel.Cells[1, iIndiceColumna].ColumnWidth = 0; } if (iIndiceColumna == 2 || iIndiceColumna == 6 || iIndiceColumna == 7 || iIndiceColumna == 12 || iIndiceColumna == 13) { excel.Cells[1, iIndiceColumna].ColumnWidth = 8; } if (iIndiceColumna == 3 || iIndiceColumna == 5 || iIndiceColumna == 9 || iIndiceColumna == 10 || iIndiceColumna == 11) { excel.Cells[1, iIndiceColumna].ColumnWidth = 10; } if (iIndiceColumna != 1) { excel.Cells[4, iIndiceColumna] = col.HeaderText; excel.Cells[4, iIndiceColumna].Interior.Color = Color.Yellow; excel.Cells[4, iIndiceColumna].BorderAround(); } } int iIndiceFila = 4; 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.get_Range("A4", "M4").BorderAround(); excel.Visible = true; } catch (Exception ex) { catchMensaje.lblMensaje.Text = ex.ToString(); catchMensaje.ShowDialog(); } }
private void btnDownload_Click(object sender, EventArgs e) { if (!this.rbtnConsumption.Checked && !this.rbtnOriginalGoods.Checked) { MessageBox.Show("Please select consumption or original goods to download first.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (this.dgvConsumption.RowCount == 0 && this.rbtnConsumption.Checked) { MessageBox.Show("The consumption data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); this.btnDownload.Focus(); return; } if (this.dgvOriginalGoods.RowCount == 0 && this.rbtnOriginalGoods.Checked) { MessageBox.Show("The original goods data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); this.btnDownload.Focus(); return; } DialogResult dlgR = MessageBox.Show("Please select a document format:\n[Yes] Generate Excel as new version;\n[No] Generate Excel as old version;\n[Cancel] Reject to handle.", "Prompt", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information); if (dlgR == DialogResult.Yes) { #region //new version if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; string[] strFiledName = { "成品备件号" }; SqlLib lib = new SqlLib(); DataTable dt = lib.SelectDistinct(middleTable, strFiledName); lib.Dispose(0); for (int i = 0; i < dt.Rows.Count; i++) { string strEHB = dt.Rows[i][0].ToString().Trim(); DataRow[] datarow = middleTable.Select("[成品备件号] = '" + strEHB + "' AND ([重复备件号] IS NULL OR [重复备件号] = '')"); if (datarow.Length > 0) { worksheet.Name = strEHB.Replace("/", "X"); worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 10]).NumberFormatLocal = "@"; for (int j = 0; j < datarow.Length; j++) { worksheet.Cells[j + 2, 1] = datarow[j][9].ToString().Trim(); worksheet.Cells[j + 2, 2] = datarow[j][0].ToString().Trim(); worksheet.Cells[j + 2, 3] = datarow[j][3].ToString().Trim(); worksheet.Cells[j + 2, 4] = datarow[j][4].ToString().Trim(); worksheet.Cells[j + 2, 5] = datarow[j][5].ToString().Trim(); worksheet.Cells[j + 2, 6] = "0"; worksheet.Cells[j + 2, 7] = datarow[j][2].ToString().Trim(); worksheet.Cells[j + 2, 8] = datarow[j][7].ToString().Trim(); worksheet.Cells[j + 2, 9] = string.Empty; } worksheet.Cells[1, 1] = "手册号"; worksheet.Cells[1, 2] = "成品货物备件号"; worksheet.Cells[1, 3] = "净耗数量"; worksheet.Cells[1, 4] = "数量损耗率"; worksheet.Cells[1, 5] = "重量损耗率"; worksheet.Cells[1, 6] = "总耗重量"; worksheet.Cells[1, 7] = "原料货物备件号"; worksheet.Cells[1, 8] = "废料货物备件号"; worksheet.Cells[1, 9] = "备注"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 9]).Font.Bold = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Name = "Verdana"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Size = 9; worksheet.Cells.EntireColumn.AutoFit(); if (i < dt.Rows.Count - 1) { object missing = System.Reflection.Missing.Value; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing); } } } dt.Clear(); dt.Dispose(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "OriginalGoods"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).NumberFormatLocal = "@"; int iActualRow = 0; for (int k = 0; k < this.dgvOriginalGoods.Rows.Count; k++) { if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, k].Value.ToString().Trim())) { iActualRow++; worksheet.Cells[iActualRow + 1, 1] = this.dgvOriginalGoods[15, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 2] = this.dgvOriginalGoods[2, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 3] = this.dgvOriginalGoods[16, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 4] = "//"; worksheet.Cells[iActualRow + 1, 5] = this.dgvOriginalGoods[17, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 6] = "1"; } } worksheet.Cells[1, 1] = "货主十位数编码"; worksheet.Cells[1, 2] = "原始货物备件号"; worksheet.Cells[1, 3] = "合并货物备件号"; worksheet.Cells[1, 4] = "规格型号"; worksheet.Cells[1, 5] = "仓库号"; worksheet.Cells[1, 6] = "单位净重"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 6]).Font.Bold = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Name = "Verdana"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Size = 9; worksheet.Cells.EntireColumn.AutoFit(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } #endregion } else if (dlgR == DialogResult.No) { #region //old version if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvConsumption.RowCount + 1, this.dgvConsumption.ColumnCount - 2]).NumberFormatLocal = "@"; int iActualRow = 0, iLineNo = 0; string strBatchFg = String.Empty; for (int i = 0; i < this.dgvConsumption.RowCount; i++) { string strBOM1 = this.dgvConsumption[0, i].Value.ToString().Trim(); if (String.Compare(strBOM1, strBatchFg) == 0) { iLineNo++; } else { iLineNo = 1; strBatchFg = strBOM1; } string strBOM2 = this.dgvConsumption[8, i].Value.ToString().Trim(); //Mapping 'BOM In Customs' decimal dConsump = Convert.ToDecimal(this.dgvConsumption[3, i].Value.ToString().Trim()); //Mapping 'Consumption' if (String.IsNullOrEmpty(strBOM2) && dConsump > 0.0M) { iActualRow++; for (int j = 0; j < this.dgvConsumption.ColumnCount - 2; j++) { if (j == 1) { excel.Cells[iActualRow + 1, j + 1] = iLineNo; } else { excel.Cells[iActualRow + 1, j + 1] = this.dgvConsumption[j, i].Value.ToString().Trim(); } } } } for (int k = 0; k < this.dgvConsumption.ColumnCount - 2; k++) { excel.Cells[1, k + 1] = this.dgvConsumption.Columns[k].HeaderText.ToString().Trim(); } excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).Font.Bold = true; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Name = "Verdana"; excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Size = 9; excel.Cells.EntireColumn.AutoFit(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvOriginalGoods.RowCount + 1, this.dgvOriginalGoods.ColumnCount - 5]).NumberFormatLocal = "@"; //Set the excel cells format as text int iActualRow = 0; for (int m = 0; m < this.dgvOriginalGoods.RowCount; m++) { if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, m].Value.ToString().Trim())) //The same mapping 'BOM In Customs' { iActualRow++; for (int n = 1; n < this.dgvOriginalGoods.ColumnCount - 4; n++) { excel.Cells[iActualRow + 1, n + 1] = this.dgvOriginalGoods[n + 1, m].Value.ToString().Trim(); } } } for (int y = 1; y <= iActualRow; y++) { excel.Cells[y + 1, 1] = y; } //Regenerate the first column value, since it is the serial number for (int x = 1; x < this.dgvOriginalGoods.ColumnCount - 4; x++) { excel.Cells[1, x] = this.dgvOriginalGoods.Columns[x].HeaderText.ToString().Trim(); } excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Bold = true; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Name = "Verdana"; excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Size = 9; excel.Cells.EntireColumn.AutoFit(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } #endregion } }
private void bar_ExportExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (this.action != "view" || this._assemblySiteDifference.Details == null || this._assemblySiteDifference.Details.Count < 1) { return; } try { Type objClassType = null; objClassType = Type.GetTypeFromProgID("Excel.Application"); if (objClassType == null) { MessageBox.Show("本機沒有安裝Excel", "提示!", MessageBoxButtons.OK); return; } ConvertMaterial(); //计算原料净重 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 7]); r.MergeCells = true;//合并单元格 excel.Cells.ColumnWidth = 10; excel.Cells[1, 1] = "组装现场盘点差异(" + this.date_Difference.DateTime.ToString("yyyy-MM-dd") + ")"; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20; //excel.Cells[1, productShipmentList.Count + 1] = DateTime.Now.ToString("yyyy.MM.dd"); excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 7]).HorizontalAlignment = -4108; excel.Cells[2, 1] = "商品编号"; excel.Cells[2, 2] = "商品名称"; excel.Cells[2, 3] = "客户型号"; excel.Cells[2, 4] = "版本"; excel.Cells[2, 5] = "盘点数量"; excel.Cells[2, 6] = "理论数量"; excel.Cells[2, 7] = "差异"; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 7 + 1 + this._assemblySiteDifference.Details[0].Product.MaterialDic.Keys.Count]).Interior.Color = "12566463"; excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 1]).ColumnWidth = 25; excel.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).ColumnWidth = 50; int col = 9; //原料 foreach (var item in this._assemblySiteDifference.Details[0].Product.MaterialDic) { excel.Cells[2, col++] = item.Key; } List <Model.AssemblySiteDifferenceDetai> haveThreeCategoryPro = this._assemblySiteDifference.Details.Where(P => P.Product.ProductCategory3 != null).ToList(); List <Model.AssemblySiteDifferenceDetai> haveTwoCategoryPro = this._assemblySiteDifference.Details.Where(P => P.Product.ProductCategory2 != null && P.Product.ProductCategory3 == null).ToList(); List <Model.AssemblySiteDifferenceDetai> haveOneCategoryPro = this._assemblySiteDifference.Details.Where(P => P.Product.ProductCategory2 == null && P.Product.ProductCategory3 == null).ToList(); int row = 3; foreach (var item in haveThreeCategoryPro.GroupBy(P => P.Product.ProductCategory3.ProductCategoryName)) { SetExcelFormat(excel, ref col, ref row, item); foreach (var pro in item) { excel.Cells[row, 1] = pro.Product.Id; excel.Cells[row, 2] = pro.Product.ProductName; excel.Cells[row, 3] = pro.Product.CustomerProductName; excel.Cells[row, 4] = pro.Product.ProductVersion; excel.Cells[row, 5] = pro.ActualQuantity; excel.Cells[row, 6] = pro.TheoryQuantity; excel.Cells[row, 7] = pro.DiffQty; col = 9; foreach (var dic in pro.Product.MaterialDic) { excel.Cells[row, col++] = dic.Value; } row++; } row++; } foreach (var item in haveTwoCategoryPro.GroupBy(P => P.Product.ProductCategory2.ProductCategoryName)) { SetExcelFormat(excel, ref col, ref row, item); foreach (var pro in item) { excel.Cells[row, 1] = pro.Product.Id; excel.Cells[row, 2] = pro.Product.ProductName; excel.Cells[row, 3] = pro.Product.CustomerProductName; excel.Cells[row, 4] = pro.Product.ProductVersion; excel.Cells[row, 5] = pro.ActualQuantity; excel.Cells[row, 6] = pro.TheoryQuantity; excel.Cells[row, 7] = pro.DiffQty; col = 9; foreach (var dic in pro.Product.MaterialDic) { excel.Cells[row, col++] = dic.Value; } row++; } row++; } foreach (var item in haveOneCategoryPro.GroupBy(P => P.Product.ProductCategory.ProductCategoryName)) { SetExcelFormat(excel, ref col, ref row, item); foreach (var pro in item) { excel.Cells[row, 1] = pro.Product.Id; excel.Cells[row, 2] = pro.Product.ProductName; excel.Cells[row, 3] = pro.Product.CustomerProductName; excel.Cells[row, 4] = pro.Product.ProductVersion; excel.Cells[row, 5] = pro.ActualQuantity; excel.Cells[row, 6] = pro.TheoryQuantity; excel.Cells[row, 7] = pro.DiffQty; col = 9; foreach (var dic in pro.Product.MaterialDic) { excel.Cells[row, col++] = dic.Value; } row++; } row++; } excel.Visible = true;//是否打开该Excel文件 excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized; } catch { MessageBox.Show("Excel未生成完畢,請勿操作,并重新點擊按鈕生成數據!", "提示!", MessageBoxButtons.OK); return; } }
private void btn2_Click(object sender, System.EventArgs e) { Microsoft.Office.Interop.Excel.Range Rng; fileExcel = @"C:\Users\hp\source\repos\WpfApp9\WpfApp9\obj\Debug\SK_Moschnost.xlsx"; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); // open workbook Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(fileExcel, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1]; Microsoft.Office.Interop.Excel.Chart xlChart; Microsoft.Office.Interop.Excel.Series xlSeries; xlApp.Visible = true; xlApp.UserControl = true; Microsoft.Office.Interop.Excel.Range usedColumn = xlSheet.UsedRange.Columns[2]; System.Array myvalues = (System.Array)usedColumn.Cells.Value2; string[] strArray = myvalues.OfType <object>().Select(o => o.ToString()).ToArray(); for (int i = 1; i < strArray.Length; i++) { data.Add(Convert.ToDouble(strArray[i])); } for (double g = gamin; g < gamax; g += 1000) { for (double T = 0; T <= tf - t0; T += 0.01) { for (double t = t0; t <= tf; t += 0.01) { function(t, T, g); } } } gamma(); minmax(); timemax(); taumin2(); gmin22(); String sMsg; sMsg = "Minimum of maximum |f(gamma,tau)|: "; sMsg = String.Concat(sMsg, minmax()); sMsg = String.Concat(sMsg, " Вт, when minimum of gamma: "); sMsg = String.Concat(sMsg, gmin22()); sMsg = String.Concat(sMsg, ", minimum of tau: "); sMsg = String.Concat(sMsg, taumin2()); sMsg = String.Concat(sMsg, ", maximum of time: "); sMsg = String.Concat(sMsg, timemax()); MessageBoxResult mes = MessageBox.Show(sMsg, "Caculate and draw graphic?", MessageBoxButton.YesNo); if (mes == MessageBoxResult.No) { Close(); } else { //Add table headers going cell by cell. xlSheet.Cells[1, 4] = "Время [t0,tf], сек."; xlSheet.Cells[1, 5] = "|f(gamma,tau)|"; //AutoFit columns A:B. Rng = xlSheet.get_Range("A1:G1"); Rng.EntireColumn.AutoFit(); // interval [t0, tf] Rng = xlApp.get_Range("D2", "D1002"); Rng.Formula = "=A101"; StreamWriter txt = new StreamWriter("testinte2.txt"); for (double t = t0; t < tf; t += 0.01) { txt.WriteLine(Math.Abs(gmin22() - data[Convert.ToInt32(t * 100)] - data[Convert.ToInt32((t + taumin2()) * 100)])); } txt.Close(); object misvalue = System.Reflection.Missing.Value; string[] txtname = System.IO.File.ReadAllLines(@"C:\Users\hp\source\repos\WpfApp9\WpfApp9\bin\Debug\testinte2.txt"); try { for (int i = 0; i <= txtname.Length; i++) { xlSheet.Cells[5][i + 2] = txtname[i]; } Thread.Sleep(3000); } catch (Exception ex) { Console.WriteLine("Exception" + ex); } // add a chart for the selected data xlWorkBook = (Microsoft.Office.Interop.Excel.Workbook)xlSheet.Parent; xlChart = (Microsoft.Office.Interop.Excel.Chart)xlWorkBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); // use the ChartWizard to create a new chart from the select data xlSeries = (Microsoft.Office.Interop.Excel.Series)xlChart.SeriesCollection(1); xlSeries.XValues = xlSheet.get_Range("E2:E1002"); } }
//masih belum public void printLaporanPenjualan(String filename, String periode) { try { excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; worKbooK = excel.Workbooks.Add(Type.Missing); worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet; worKsheeT.Name = "Report"; worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 6]].Merge(); worKsheeT.Cells[1, 1] = "Laporan Penjualan"; worKsheeT.Cells.Font.Size = 15; worKsheeT.Cells[2, 1] = "Tanggal"; worKsheeT.Cells[2, 2] = "NoNota"; worKsheeT.Cells[2, 3] = "Nama Barang"; worKsheeT.Cells[2, 4] = "Jumlah"; worKsheeT.Cells[2, 5] = "Harga"; worKsheeT.Cells[2, 6] = "Total"; //worKsheeT.Cells[2, 5] = "Kategori"; Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle"); style.Font.Name = "Verdana"; style.Font.Size = 12; style.Font.Bold = true; style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Koneksi k = new Koneksi(); k.Connect(); int baris = 3; String q = "SELECT DATE_FORMAT(Tanggal,'%d-%M-%Y') as Tanggal,b.Nama,nd.Jumlah,nd.Harga,(nd.Harga*nd.Jumlah) as Subtotal,nb.NoNota " + "FROM notajual nb " + "INNER JOIN notajualdetil nd ON (nb.NoNota=nd.NoNota) " + "INNER JOIN barang b ON (b.KodeBarang=nd.KodeBarang) " + "WHERE DATE_FORMAT(Tanggal,'%Y-%M')=?periode"; MySqlCommand c = new MySqlCommand(q, k.KoneksiDB); c.Parameters.Add(new MySqlParameter("periode", periode)); MySqlDataReader rd = c.ExecuteReader(); int total = 0; while (rd.Read()) { worKsheeT.Cells[baris, 1] = rd["Tanggal"]; worKsheeT.Cells[baris, 2] = rd["NoNota"]; worKsheeT.Cells[baris, 3] = rd["Nama"]; worKsheeT.Cells[baris, 4] = rd["Jumlah"]; worKsheeT.Cells[baris, 5] = rd["Harga"]; worKsheeT.Cells[baris, 6] = rd["Subtotal"]; total = total + Int32.Parse(rd["Subtotal"] + ""); baris = baris + 1; } worKsheeT.Cells[baris, 6] = total; Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("E3:F" + baris); angkaStyles.NumberFormat = "##,#"; Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:F20"); Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:F2"); Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:F" + baris); rangeStyles2.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles.Value2 = "'Style Test"; //rangeStyles.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles3.BorderAround2(); rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; k.KoneksiDB.Close(); worKbooK.SaveAs(filename); worKbooK.Close(); excel.Quit(); ProcessStartInfo info = new ProcessStartInfo(); info.Verb = "print"; info.FileName = @filename; info.CreateNoWindow = true; info.WindowStyle = ProcessWindowStyle.Hidden; Process p = new Process(); p.StartInfo = info; p.Start(); p.WaitForInputIdle(); System.Threading.Thread.Sleep(3000); //if (false == p.CloseMainWindow()) //p.Kill(); } catch (Exception ex) { String msg = ex.Message; String abc = "aa"; } finally { worKsheeT = null; celLrangE = null; worKbooK = null; } }
//Función para exportar a excel private void exportarAExcel(DataGridView dgvCierre) { try { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int iIndiceColumna = 0; excel.Columns.ColumnWidth = 10; excel.Cells[1, 1] = "EMPRESA: "; excel.Cells[1, 2] = "COSA NOSTRA"; excel.Cells[2, 1] = "REPORTE:"; excel.Cells[2, 2] = "KARDEX POR EMPRESA"; excel.Cells[3, 1] = "PROD. INICIAL:"; excel.Cells[3, 2] = dbAyudaArticuloInicial.txtIdentificacion.Text; excel.Cells[4, 1] = "PROD. FINAL:"; excel.Cells[4, 2] = dbAyudaArticuloFinal.txtIdentificacion.Text; excel.Cells[5, 1] = "FECHA INICIO"; excel.Cells[5, 2] = txtFechaDesde.Text; excel.Cells[5, 4] = "FECHA FIN"; excel.Cells[5, 5] = txtFechaHasta.Text; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; if (iIndiceColumna == 2 || iIndiceColumna == 3) { excel.Cells[8, iIndiceColumna].ColumnWidth = 15; } excel.Cells[8, iIndiceColumna] = col.HeaderText; excel.Cells[8, iIndiceColumna].Interior.Color = Color.Yellow; excel.Cells[8, iIndiceColumna].BorderAround(); } int iIndiceFila = 9; 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.get_Range("A8", "T8").BorderAround(); excel.Visible = true; } catch (Exception ex) { catchMensaje.lblMensaje.Text = ex.ToString(); catchMensaje.ShowDialog(); } }
private void backgroundWorkerYearExl_DoWork(object sender, DoWorkEventArgs e) { try { progressBarYear.Value = 10; progressBarYear.Update(); labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%"; labelProgYear.Update(); string yr_str = comboBoxYear3.Text.Substring(2, 2); //建立Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;//一个xls文档 new Microsoft.Office.Interop.Excel.Workbooks(); Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook Microsoft.Office.Interop.Excel.Worksheet ws;//excel中的一个sheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"]; ws.Name = yr_str + "年总合计"; #region 年总计 Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 11]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[4, 1]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 2], excel.Cells[4, 2]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 3], excel.Cells[4, 3]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 10], excel.Cells[4, 10]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 11], excel.Cells[4, 11]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 4], excel.Cells[3, 6]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 7], excel.Cells[3, 9]); merge_range.Merge(Type.Missing); excel.Cells[1, 1] = comboBoxYear3.Text +"年垃圾产量总计表"; Microsoft.Office.Interop.Excel.Range bold_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2,11]); bold_range.Font.Size = 20; bold_range.Font.Bold = true; bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; bold_range.EntireColumn.AutoFit(); //自动调整列宽 bold_range.EntireRow.AutoFit(); excel.Cells[3, 1] = "月份"; excel.Cells[3, 2] = "月产箱数"; excel.Cells[3, 3] = "实际吨数"; excel.Cells[3, 4] = "自运箱数"; excel.Cells[3, 5] = "实际吨数"; excel.Cells[3, 10] = "合计箱数"; excel.Cells[3, 11] = "合计吨数"; excel.Cells[4, 4] = "西清"; excel.Cells[4, 5] = "十队"; excel.Cells[4, 6] = "合计"; excel.Cells[4, 7] = "西清"; excel.Cells[4, 8] = "十队"; excel.Cells[4, 9] = "合计"; //填充数据 for (int x = 0; x < dataGridViewMon.RowCount; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x + 5, y + 1] = dataGridViewMon[y, x].Value; } else excel.Cells[x + 5, y + 1] = ""; } } Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[17, 11]);//现有的 all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; all_range.EntireColumn.AutoFit(); //自动调整列宽 all_range.EntireRow.AutoFit(); all_range.Borders.LineStyle = 1; all_range.Font.Size = 12; #endregion progressBarYear.Value = 20; progressBarYear.Update(); labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%"; labelProgYear.Update(); dataGridViewMon.DataSource = crform_ds.Tables["MonOutPut"]; for (int cur_sheet = 1; cur_sheet <= 12; cur_sheet++) { progressBarYear.Value = Convert.ToInt32(20 + 6.5*cur_sheet); progressBarYear.Update(); labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%"; labelProgYear.Update(); ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet; ws.Name = yr_str + "年" + cur_sheet.ToString()+"月"; merge_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 11]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[4, 1]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 2], excel.Cells[4, 2]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 3], excel.Cells[4, 3]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 10], excel.Cells[4, 10]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 11], excel.Cells[4, 11]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 4], excel.Cells[3, 6]); merge_range.Merge(Type.Missing); merge_range = excel.get_Range(excel.Cells[3, 7], excel.Cells[3, 9]); merge_range.Merge(Type.Missing); excel.Cells[1, 1] = comboBoxYear3.Text + "年"+cur_sheet.ToString()+"月垃圾产量明细表"; bold_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 11]); bold_range.Font.Size = 20; bold_range.Font.Bold = true; bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; bold_range.EntireColumn.AutoFit(); //自动调整列宽 bold_range.EntireRow.AutoFit(); excel.Cells[3, 1] = "日期"; excel.Cells[3, 2] = "日产箱数"; excel.Cells[3, 3] = "实际吨数"; excel.Cells[3, 4] = "自运箱数"; excel.Cells[3, 5] = "实际吨数"; excel.Cells[3, 10] = "合计箱数"; excel.Cells[3, 11] = "合计吨数"; excel.Cells[4, 4] = "西清"; excel.Cells[4, 5] = "十队"; excel.Cells[4, 6] = "合计"; excel.Cells[4, 7] = "西清"; excel.Cells[4, 8] = "十队"; excel.Cells[4, 9] = "合计"; //填充数据 for (int x = (cur_sheet - 1) * 32; x < (cur_sheet - 1) * 32+32; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null && y != 3 ) { excel.Cells[x - (cur_sheet - 1) * 32 + 5, y + 1] = dataGridViewMon[y, x].Value; } else excel.Cells[x - (cur_sheet - 1) * 32 + 5, y + 1] = ""; } } all_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[36, 11]);//现有的 all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; all_range.EntireColumn.AutoFit(); //自动调整列宽 all_range.EntireRow.AutoFit(); all_range.Borders.LineStyle = 1; all_range.Font.Size = 12; }//end for每个sheet labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%"; labelProgYear.Update(); progressBarYear.Value = progressBarYear.Maximum; progressBarYear.Update(); wb.Saved = true; wb.SaveCopyAs(fName); //保存 excel.Quit(); //关闭进程 labelProgYear.Text = "已完成"; labelProgYear.Update(); MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None); //label5.Text = "完成"; toolStripButton6.Enabled = false; this.Enabled = true; } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); progressBarYear.Visible = false; progressBarYear.Update(); labelProgYear.Text = ""; labelProgYear.Update(); this.Enabled = true; } this.Enabled = true; }
/// <summary>根据参数创建excel表</summary> public void CreateExcel(System.Data.DataTable dt, string pathname, string[] str) { int eRowIndex = 2; int eColIndex = 1; int cols = dt.Columns.Count; int rows = dt.Rows.Count; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets["sheet1"]; //xlSheet.PageSetup.CenterHorizontally = false;//页面水平居中//xlSheet.PageSetup.CenterVertically = false;//页面不垂直居中 xlApp.Cells[1, cols] = str[0]; xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).Font.Bold = true; xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).Font.Size = str[1]; xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).MergeCells = true; xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; try { //列名的处理 for (int i = 0; i < cols; i++) { xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName; eColIndex++; } //列内容样式 xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, cols]).Font.Bold = true; xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, cols]).Font.Name = str[3]; xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, cols]).Font.Size = str[2]; //xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, 1]).ColumnWidth = 20;//设置列宽 eRowIndex++; for (int i = 0; i < rows; i++) { eColIndex = 1; for (int j = 0; j < cols; j++) { xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString(); eColIndex++; } eRowIndex++; } Microsoft.Office.Interop.Excel.Range range1 = (Microsoft.Office.Interop.Excel.Range)xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[eRowIndex - 1, cols]); range1.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //xlApp.Visible = true; xlApp.Cells[eRowIndex + 1, 1] = "附加信息:"; xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true; xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true; xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).RowHeight = str[4]; xlApp.Cells[eRowIndex + 2, 1] = "确认签名:"; xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true; xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true; //行、列自适应 xlApp.Cells.EntireColumn.AutoFit(); //xlApp.Cells.EntireRow.AutoFit(); //xlApp.DisplayAlerts = true; //xlBook.SaveCopyAs(path); xlSheet.SaveAs(pathname); xlApp.Workbooks.Close(); } catch (Exception e) { Response.Write("<script>alert('0000x1:'" + e.Message + ")</script>"); } finally { xlApp.Quit(); GC.Collect(); //杀掉Excel进程。 } }
private void timerMon5_Tick(object sender, EventArgs e) { if (flag_everydayexl == true) { this.dateTimePicker6.Enabled = false; timerMon5.Stop(); dataGridViewMon.DataSource = crform_ds.Tables["Result_t0"]; //MessageBox.Show(dataGridView1[0, 0].Value.ToString()); //MessageBox.Show(dataGridView1[0, 1].Value.ToString()); //MessageBox.Show(dataGridView1[1, 0].Value.ToString()); //MessageBox.Show(dataGridView1[1, 1].Value.ToString()); //ExportExcel(dataGridView1); try { if (dataGridViewMon.Rows.Count >= 1) { this.Enabled = false; //MessageBox.Show("F**K@@"); progressBarDay.Visible = true; progressBarDay.Value = 0; progressBarDay.Update(); labelProgDay.Text = "开始导出"; labelProgDay.Update(); string yr_str = comboBoxYear2.Text.Trim(); string mon_str = comboBoxMon2.Text.Trim(); string day_str = comboBoxDay2.Text.Trim(); string fName = ""; SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.InitialDirectory = "d:"; saveFileDialog.Filter = "EXCEL文件|*.xlsx"; saveFileDialog.FilterIndex = 2; saveFileDialog.FileName = mon_str + "月" + day_str + "日完成情况"; saveFileDialog.RestoreDirectory = true; if (saveFileDialog.ShowDialog() == DialogResult.OK) { fName = saveFileDialog.FileName; progressBarDay.Value = 12; progressBarDay.Update(); this.Refresh(); //建立Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;//一个xls文档 new Microsoft.Office.Interop.Excel.Workbooks(); Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook Microsoft.Office.Interop.Excel.Worksheet ws;//excel中的一个sheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"]; //int num_sheet = dgv.Rows.Count / 55; //label5.Text = "完成 " + progressBar1.Value.ToString() + "%"; //label5.Update(); //for (int cur_sheet = 1; cur_sheet <= num_sheet; cur_sheet++) //{ // if (num_sheet != 1) // { // progressBar1.Value = Convert.ToInt32(12 + cur_sheet * 87 / num_sheet); // progressBar1.Update(); //label5.Text = "完成 " + progressBar1.Value.ToString() + "%"; //label5.Update(); // } // else // { // progressBar1.Value = 50; // progressBar1.Update(); //label5.Text = "完成 " + progressBar1.Value.ToString() + "%"; //label5.Update(); // } // if (cur_sheet == 1) // ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"]; // else // ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet; // ws.Name = cur_sheet.ToString(); excel.Cells[2, 1] = "一班:王玉宝"; excel.Cells[2, 6] = "二班:蔡震"; excel.Cells[2, 11] = "三班:韩培元"; excel.Cells[2, 13] = "日期:" + mon_str + "月" + day_str + "日"; Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[2, 13], excel.Cells[2, 14]); merge_range.Merge(Type.Missing); Microsoft.Office.Interop.Excel.Range bold_range = excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 15]); bold_range.Font.Bold = true; //生成字段名称 excel.Cells[3, 1] = "楼名"; excel.Cells[3, 2] = "日产箱数"; excel.Cells[3, 3] = "实际吨数"; excel.Cells[3, 4] = "平均吨数"; excel.Cells[3, 6] = "楼名"; excel.Cells[3, 7] = "日产箱数"; excel.Cells[3, 8] = "实际吨数"; excel.Cells[3, 9] = "平均吨数"; excel.Cells[3, 11] = "楼名"; excel.Cells[3, 12] = "日产箱数"; excel.Cells[3, 13] = "实际吨数"; excel.Cells[3, 14] = "平均吨数"; //填充数据 for (int x = 0; x < 15; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x + 4, y + 1] = dataGridViewMon[y, x].Value; } else excel.Cells[x + 4, y + 1] = ""; } } for (int x = 18; x < 40; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x - 18 + 4, y + 1 + 5] = dataGridViewMon[y, x].Value; } else excel.Cells[x - 18 + 4, y + 1 + 5] = ""; } } for (int x = 43; x < 59; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x - 43 + 4, y + 1 + 10] = dataGridViewMon[y, x].Value; } else excel.Cells[x - 43 + 4, y + 1 + 10] = ""; } } for (int x = 15; x < 18; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x + 13, y + 1] = dataGridViewMon[y, x].Value; } else excel.Cells[x + 13, y + 1] = ""; } } for (int x = 40; x < 43; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x - 12, y + 1 + 5] = dataGridViewMon[y, x].Value; } else excel.Cells[x - 12, y + 1 + 5] = ""; } } for (int x = 59; x < 62; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x - 31, y + 1 + 10] = dataGridViewMon[y, x].Value; } else excel.Cells[x - 31, y + 1 + 10] = ""; } } Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[30, 14]);//现有的 //all_range.NumberFormatLocal = "0.00_"; all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; all_range.EntireColumn.AutoFit(); //自动调整列宽 all_range.EntireRow.AutoFit(); Microsoft.Office.Interop.Excel.Range t1_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[30, 4]); t1_range.Borders.LineStyle = 1; Microsoft.Office.Interop.Excel.Range t2_range = excel.get_Range(excel.Cells[3, 6], excel.Cells[30, 9]); t2_range.Borders.LineStyle = 1; Microsoft.Office.Interop.Excel.Range t3_range = excel.get_Range(excel.Cells[3, 11], excel.Cells[30, 14]); t3_range.Borders.LineStyle = 1; labelProgDay.Text = "完成 " + progressBarDay.Value.ToString() + "%"; labelProgDay.Update(); //}//end for每个datagridview,也就是每个table_team //}//end for每个sheet progressBarDay.Value = progressBarDay.Maximum; progressBarDay.Update(); wb.Saved = true; wb.SaveCopyAs(fName); //保存 excel.Quit(); //关闭进程 labelProgDay.Text = "已完成"; labelProgDay.Update(); MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None); //label5.Text = "完成"; toolStripButtonDayCompExl.Enabled = false; this.Enabled = true; } else { progressBarDay.Visible = false; progressBarDay.Update(); labelProgDay.Text = ""; labelProgDay.Update(); MessageBox.Show("未导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None); } } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); progressBarDay.Visible = false; progressBarDay.Update(); labelProgDay.Text = ""; labelProgDay.Update(); this.Enabled = true; } this.Enabled = true; this.dateTimePicker6.Enabled = true; flag_everydayexl = false; } }
//基本对比函数,新旧报表左侧标题必须一样 private void DoCompare(Microsoft.Office.Interop.Excel.Application excelRes, Microsoft.Office.Interop.Excel.Application excelOld, Microsoft.Office.Interop.Excel.Application excelNew, int iStartRowID, int iStartColID) { Microsoft.Office.Interop.Excel.Worksheet sheetNew = excelNew.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; Microsoft.Office.Interop.Excel.Worksheet sheetOld = excelOld.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; int iNullRowCnt = 0; int iNullColCnt = 0; object MissingValue = System.Type.Missing; for (int i = iStartRowID; i <= sheetNew.UsedRange.Rows.Count; i++) { object objTmp = ((Microsoft.Office.Interop.Excel.Range)sheetOld.UsedRange.Cells[i, 1]).Text; string strTmp = "";// excelNew.Cells[i, 1].ToString(); if (objTmp != null) { strTmp = objTmp.ToString(); } if (strTmp == "") { iNullRowCnt++; if (iNullRowCnt > 50) { break; } } else { iNullRowCnt = 0; } bool isSame = true; if (iStartColID > 1) { string strLast = ""; object objLast = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[i, iStartColID - 1]).Text; if (objLast != null) { strLast = objLast.ToString(); } for (int j = 1; j < iStartColID; j++) { object objOld = ((Microsoft.Office.Interop.Excel.Range)sheetOld.UsedRange.Cells[i, j]).Text; object objNew = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[i, j]).Text; string strOld = ""; string strNew = ""; if (objOld != null) { strOld = objOld.ToString(); } if (objNew != null) { strNew = objNew.ToString(); } if (strOld.Trim() != strNew.Trim()) { isSame = false; } else { excelRes.Cells[i, j] = strOld; if (strOld == "" && i > 1 && strLast != "") { excelRes.get_Range(excelRes.Cells[i - 1, j], excelRes.Cells[i, j]).Merge(MissingValue); } } } if (isSame) { for (int j = iStartColID; j <= sheetNew.UsedRange.Columns.Count; j++) { object objTmpOld = ((Microsoft.Office.Interop.Excel.Range)sheetOld.UsedRange.Cells[i, j]).Text; object objTmpNew = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[i, j]).Text; object objTmp1 = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[iStartRowID - 1, j]).Text; string strTmpOld = "";; string strTmpNew = ""; string strTmp1 = ""; if (objTmpOld != null) { strTmpOld = objTmpOld.ToString(); } if (objTmpNew != null) { strTmpNew = objTmpNew.ToString(); } if (objTmp1 != null) { strTmp1 = objTmp1.ToString(); } if (strTmp1 == "" && strTmpOld == "" && strTmpNew == "") { iNullColCnt++; if (iNullColCnt > 50) { break; } } else { iNullColCnt = 0; } string strRes = ""; try { double dOld = 0; if (strTmpOld != "") { dOld = double.Parse(strTmpOld); } double dNew = 0; if (strTmpNew != "") { dNew = double.Parse(strTmpNew); } double dRes = dNew - dOld; strRes = dRes.ToString("f2"); if (dRes > 0) { excelRes.Cells[i, j] = "'+" + strRes; } else if (dRes < 0) { excelRes.Cells[i, j] = "'" + strRes; } } catch { } } } } } }
private void backgroundWorkerMonCheCiExl_DoWork(object sender, DoWorkEventArgs e) { try { progressBarMonCheCi.Value = 60; progressBarMonCheCi.Update(); labelProgMonCheCi.Text = "正在处理..."; labelProgMonCheCi.Update(); //建立Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;//一个xls文档 new Microsoft.Office.Interop.Excel.Workbooks(); Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook Microsoft.Office.Interop.Excel.Worksheet ws;//excel中的一个sheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"]; Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[1, 2], excel.Cells[1, 33]); merge_range.Merge(Type.Missing); excel.Cells[1, 1] = comboBox9.Text; excel.Cells[1, 2] = comboBoxYear4.Text + "年" + comboBoxMon4.Text + "月份完成任务情况统计"; Microsoft.Office.Interop.Excel.Range bold_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]); bold_range.Font.Size = 16; bold_range.Font.Bold = true; bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; bold_range.EntireColumn.AutoFit(); //自动调整列宽 bold_range.EntireRow.AutoFit(); bold_range = excel.get_Range(excel.Cells[1, 2], excel.Cells[1, 33]); bold_range.Font.Size = 20; bold_range.Font.Bold = true; bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; bold_range.EntireColumn.AutoFit(); //自动调整列宽 bold_range.EntireRow.AutoFit(); excel.Cells[2, 1] = "站名"; excel.Cells[2, 2] = "1"; excel.Cells[2, 3] = "2"; excel.Cells[2, 4] = "3"; excel.Cells[2, 5] = "4"; excel.Cells[2, 6] = "5"; excel.Cells[2, 7] = "6"; excel.Cells[2, 8] = "7"; excel.Cells[2, 9] = "8"; excel.Cells[2, 10] = "9"; excel.Cells[2, 11] = "10"; excel.Cells[2, 12] = "11"; excel.Cells[2, 13] = "12"; excel.Cells[2, 14] = "13"; excel.Cells[2, 15] = "14"; excel.Cells[2, 16] = "15"; excel.Cells[2, 17] = "16"; excel.Cells[2, 18] = "17"; excel.Cells[2, 19] = "18"; excel.Cells[2, 20] = "19"; excel.Cells[2, 21] = "20"; excel.Cells[2, 22] = "21"; excel.Cells[2, 23] = "22"; excel.Cells[2, 24] = "23"; excel.Cells[2, 25] = "24"; excel.Cells[2, 26] = "25"; excel.Cells[2, 27] = "26"; excel.Cells[2, 28] = "27"; excel.Cells[2, 29] = "28"; excel.Cells[2, 30] = "29"; excel.Cells[2, 31] = "30"; excel.Cells[2, 32] = "31"; excel.Cells[2, 33] = "合计"; excel.Cells[2, 34] = "实际"; excel.Cells[2, 35] = "差额"; //填充数据 for (int x = 0; x < dataGridViewMon.RowCount; x++) { for (int y = 0; y < dataGridViewMon.ColumnCount; y++) { if (dataGridViewMon[y, x].Value != null) { //MessageBox.Show(dataGridView1[y, x].Value.ToString()); excel.Cells[x + 3, y + 1] = dataGridViewMon[y, x].Value; } else excel.Cells[x + 3, y + 1] = ""; } } Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[2, 1], excel.Cells[1 + dataGridViewMon.RowCount, 35]);//现有的 all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter; all_range.EntireColumn.AutoFit(); //自动调整列宽 all_range.EntireRow.AutoFit(); all_range.Borders.LineStyle = 1; all_range.Font.Size = 12; progressBarMonCheCi.Value =100; progressBarMonCheCi.Update(); labelProgMonCheCi.Text = "正在处理..."; labelProgMonCheCi.Update(); wb.Saved = true; wb.SaveCopyAs(fName); //保存 excel.Quit(); //关闭进程 labelProgMonCheCi.Text = "已完成"; labelProgMonCheCi.Update(); MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None); toolStripButtonMonCheCiExl.Enabled = false; this.Enabled = true; } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); progressBarMonCheCi.Visible = false; progressBarMonCheCi.Update(); labelProgMonCheCi.Text = ""; labelProgMonCheCi.Update(); this.Enabled = true; } this.Enabled = true; }
private void btn_OK_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(CustomerIds)) { MessageBox.Show("客戶不能為空", "提示", MessageBoxButtons.OK); return; } if (this.date_Start.EditValue == null || this.date_End.EditValue == null) { MessageBox.Show("日期區間不能為空", "提示", MessageBoxButtons.OK); return; } DataTable dt = invoiceXODetailManager.ShipmentTable(CustomerIds, date_Start.DateTime, date_End.DateTime); //導出Excel try { if (dt == null || dt.Rows.Count == 0) { MessageBox.Show("無數據", "提示", MessageBoxButtons.OK); return; } Type objClassType = null; objClassType = Type.GetTypeFromProgID("Excel.Application"); if (objClassType == null) { MessageBox.Show("本機沒有安裝Excel", "提示!", MessageBoxButtons.OK); return; } Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1]; sheet.Cells.ColumnWidth = 15; sheet.Cells.RowHeight = 20; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 3]).ColumnWidth = 20; excel.get_Range(excel.Cells[1, 8], excel.Cells[1, 11]).ColumnWidth = 9; excel.get_Range(excel.Cells[1, 1], excel.Cells[1 + dt.Rows.Count, 13]).Borders.LineStyle = 1; excel.get_Range(excel.Cells[1, 1], excel.Cells[1 + dt.Rows.Count, 13]).HorizontalAlignment = -4108; excel.Cells[1, 1] = "出货客戶名稱"; excel.Cells[1, 2] = "訂單號碼"; excel.Cells[1, 3] = "產品型號"; excel.Cells[1, 4] = "數量"; //excel.Cells[1, 5] = "單位PCS/PRS"; excel.Cells[1, 5] = "單位"; excel.Cells[1, 6] = "訂單交貨日"; excel.Cells[1, 7] = "盒裝/箱裝"; excel.Cells[1, 8] = "淨重KG"; excel.Cells[1, 9] = "毛重KG"; excel.Cells[1, 10] = "才積"; excel.Cells[1, 11] = "箱數"; excel.Cells[1, 12] = "外箱編號"; excel.Cells[1, 13] = "生產站"; int row = 2; double startNumber = 0; excel.get_Range(excel.Cells[2, 2], excel.Cells[1 + dt.Rows.Count, 2]).NumberFormatLocal = "@"; for (int i = 0; i < dt.Rows.Count; i++) { if (i > 0 && dt.Rows[i]["CustomerInvoiceXOId"].ToString() != dt.Rows[i - 1]["CustomerInvoiceXOId"].ToString()) //新的訂單編號,箱號重新開始排 { startNumber = 0; } double boxCount = Math.Ceiling(Convert.ToDouble(dt.Rows[i]["BoxCount"])); dt.Rows[i]["BoxNumber"] = string.Format("{0} ~ {1}", startNumber + 1, startNumber + boxCount); startNumber += boxCount; excel.Cells[row, 1] = dt.Rows[i]["CustomerFullName"] == null ? "" : dt.Rows[i]["CustomerFullName"].ToString(); excel.Cells[row, 2] = dt.Rows[i]["CustomerInvoiceXOId"] == null ? "" : dt.Rows[i]["CustomerInvoiceXOId"].ToString(); excel.Cells[row, 3] = dt.Rows[i]["CustomerProductName"] == null ? "" : dt.Rows[i]["CustomerProductName"].ToString(); excel.Cells[row, 4] = dt.Rows[i]["InvoiceXODetailQuantity"] == null ? "" : dt.Rows[i]["InvoiceXODetailQuantity"].ToString(); excel.Cells[row, 5] = dt.Rows[i]["SellUnit"] == null ? "" : dt.Rows[i]["SellUnit"].ToString(); excel.Cells[row, 6] = dt.Rows[i]["InvoiceYjrq"] == null ? "" : dt.Rows[i]["InvoiceYjrq"].ToString(); excel.Cells[row, 7] = dt.Rows[i]["Guige"] == null ? "" : dt.Rows[i]["Guige"].ToString(); excel.Cells[row, 8] = dt.Rows[i]["NetWeight"] == null ? "" : dt.Rows[i]["NetWeight"].ToString(); excel.Cells[row, 9] = dt.Rows[i]["GrossWeight"] == null ? "" : dt.Rows[i]["GrossWeight"].ToString(); excel.Cells[row, 10] = dt.Rows[i]["Volume"] == null ? "" : dt.Rows[i]["Volume"].ToString(); excel.Cells[row, 11] = dt.Rows[i]["BoxCount"] == null ? "" : dt.Rows[i]["BoxCount"].ToString(); excel.Cells[row, 12] = dt.Rows[i]["BoxNumber"] == null ? "" : dt.Rows[i]["BoxNumber"].ToString(); excel.Cells[row, 13] = dt.Rows[i]["Workhousename"] == null ? "" : dt.Rows[i]["Workhousename"].ToString(); row++; } excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized; excel.Visible = true; } catch (Exception ex) { MessageBox.Show("Excel未生成完畢,請勿操作,并重新點擊按鈕生成數據!", "提示!", MessageBoxButtons.OK); return; } }
public void printPegawai(String filename) { try { excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; worKbooK = excel.Workbooks.Add(Type.Missing); worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet; worKsheeT.Name = "Report Pegawai"; worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 4]].Merge(); worKsheeT.Cells[1, 1] = "Laporan Pegawai"; worKsheeT.Cells.Font.Size = 15; worKsheeT.Cells[2, 1] = "Kode Pegawai"; worKsheeT.Cells[2, 2] = "Nama"; worKsheeT.Cells[2, 3] = "Tanggal Lahir"; worKsheeT.Cells[2, 4] = "Alamat"; worKsheeT.Cells[2, 5] = "Gaji"; worKsheeT.Cells[2, 6] = "Username"; worKsheeT.Cells[2, 7] = "Password"; worKsheeT.Cells[2, 8] = "IdJabatan"; //worKsheeT.Cells[2, 5] = "Kategori"; Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle"); style.Font.Name = "Verdana"; style.Font.Size = 12; style.Font.Bold = true; style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Koneksi k = new Koneksi(); k.Connect(); int baris = 3; String q = "SELECT * FROM Pegawai"; MySqlCommand c = new MySqlCommand(q, k.KoneksiDB); MySqlDataReader rd = c.ExecuteReader(); while (rd.Read()) { worKsheeT.Cells[baris, 1] = rd["KodePegawai"]; worKsheeT.Cells[baris, 2] = rd["Nama"]; worKsheeT.Cells[baris, 3] = rd["TglLahir"]; worKsheeT.Cells[baris, 4] = rd["Alamat"]; worKsheeT.Cells[baris, 5] = rd["Gaji"]; worKsheeT.Cells[baris, 6] = rd["Username"]; worKsheeT.Cells[baris, 7] = rd["Password"]; worKsheeT.Cells[baris, 8] = rd["IdJabatan"]; /*if (worKsheeT.Cells[baris, 8] = "J1") * { * worKsheeT.Cells[baris, 8] = "Pegawai Pembelian"; * } * else if (worKsheeT.Cells[baris, 8] = "J2") * { * worKsheeT.Cells[baris, 8] = "Kasir"; * } * else if (worKsheeT.Cells[baris, 8] = "J3") * { * worKsheeT.Cells[baris, 8] = "Manager"; * }*/ baris = baris + 1; } Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("A3:H" + baris); angkaStyles.NumberFormat = "##,#"; Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:H20"); Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:H2"); Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:H" + baris); rangeStyles2.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles.Value2 = "'Style Test"; //rangeStyles.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles3.BorderAround2(); rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; k.KoneksiDB.Close(); /* * MySqlConnection mc = new MySqlConnection(Koneksi.strCon); * MySqlCommand cmd = new MySqlCommand(q, mc); * MySqlDataReader rd = cmd.ExecuteReader(); * * * int barisS= 2; * while (rd.Read()) * { * worKsheeT.Cells[barisS, 1] = rd["KodeBarang"]; * worKsheeT.Cells[barisS, 2] = rd["NamaBarang"]; * worKsheeT.Cells[barisS, 3] = rd["HargaJual"]; * worKsheeT.Cells[barisS, 4] = rd["Stok"]; * barisS++; * //worKsheeT.Cells[2, 5] = "Kategori"; * }*/ //DateTime dt = DateTime.Now; worKbooK.SaveAs(filename); worKbooK.Close(); excel.Quit(); /*ProcessStartInfo info = new ProcessStartInfo(); * info.Verb = "print"; * info.FileName = @filename; * info.CreateNoWindow = true; * info.WindowStyle = ProcessWindowStyle.Hidden; * * Process p = new Process(); * p.StartInfo = info; * p.Start(); * * p.WaitForInputIdle(); * System.Threading.Thread.Sleep(3000);*/ //if (false == p.CloseMainWindow()) //p.Kill(); } catch (Exception ex) { } finally { worKsheeT = null; celLrangE = null; worKbooK = null; } }