/// <summary> /// 默认生成模板样式 /// </summary> public void FormatTamplate() { ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B1", "G1"); rang.Select(); rang.Merge(false); rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "G2"); rang.Select(); rang.Merge(false); rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B3", "G3"); rang.Select(); rang.Merge(false); ws.Cells.set_Item(1, 1, "输出表"); ws.Cells.set_Item(2, 1, "表名称"); ws.Cells.set_Item(3, 1, "备注"); ws.Cells.set_Item(3, 2, this._tableName); ws.Cells.set_Item(4, 1, "字段编号"); ws.Cells.set_Item(4, 2, "字段"); ws.Cells.set_Item(4, 3, "数据元素"); ws.Cells.set_Item(4, 4, "长度"); ws.Cells.set_Item(4, 5, "小数位"); ws.Cells.set_Item(4, 6, "类型"); ws.Cells.set_Item(4, 7, "字段含义"); ws.Columns.AutoFit(); }
/// <summary> /// 合并单元格,填写值并设置边框 /// </summary> /// <param name="worksheet"></param> /// <param name="value">单元格的值</param> /// <param name="sRow">开始行</param> /// <param name="sCol">开始列</param> /// <param name="endRow">结束行</param> /// <param name="endCol">结束列</param> public static void MergeCells2(this Microsoft.Office.Interop.Excel.Worksheet worksheet, object value, int sRow, int sCol, int endRow, int endCol) { Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[sRow, sCol], worksheet.Cells[endRow, endCol]); range.Merge(); range.Value = value; range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; }
private void mergeAndCenter(Microsoft.Office.Interop.Excel.Range theRange) { //this function merges and centers the selected range of cells theRange.Merge(); theRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; theRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; return; }
//打印填数据 private void fill_excel(Microsoft.Office.Interop.Excel._Worksheet mysheet, Microsoft.Office.Interop.Excel._Workbook mybook) { int ind = 0; if (dataGridView2.Rows.Count > 12) { //在第6行插入 for (int i = 0; i < dataGridView2.Rows.Count - 12; i++) { Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mysheet.Rows[6, Type.Missing]; range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); Microsoft.Office.Interop.Excel.Range range1 = mysheet.get_Range("F6"); range1.Merge(mysheet.get_Range("G6")); } ind = dataGridView1.Rows.Count - 12; } //外表信息 mysheet.Cells[3, 8].Value = dtOuter.Rows[0]["生产指令编号"].ToString(); mysheet.Cells[4, 8].Value = dtp开始生产时间.Value.ToShortDateString() + "--" + dtp结束生产时间.Value.ToShortDateString(); //内表2信息,生产记录 for (int i = 0; i < dataGridView2.Rows.Count; i++) { mysheet.Cells[6 + i, 6] = dataGridView2.Rows[i].Cells[0].Value.ToString(); mysheet.Cells[6 + i, 8] = dataGridView2.Rows[i].Cells[1].Value.ToString(); mysheet.Cells[6 + i, 9] = dataGridView2.Rows[i].Cells[2].Value.ToString(); } //内表1,目录 for (int i = 0; i < dataGridView1.Rows.Count; i++) { string s; if (dataGridView1.Rows[i].Cells[4].Value != null) { s = dataGridView1.Rows[i].Cells[4].Value.ToString(); } else { s = "0"; } mysheet.Cells[5 + i, 1] = dataGridView1.Rows[i].Cells[2].Value.ToString(); mysheet.Cells[5 + i, 2] = dataGridView1.Rows[i].Cells[3].Value.ToString(); mysheet.Cells[5 + i, 3] = s; } //外表,汇总,审核,批准 mysheet.Cells[18 + ind, 7] = dtOuter.Rows[0]["汇总人"].ToString(); mysheet.Cells[18 + ind, 9] = DateTime.Parse(dtOuter.Rows[0]["汇总时间"].ToString()).ToString("D"); mysheet.Cells[20 + ind, 7] = dtOuter.Rows[0]["审核人"].ToString(); mysheet.Cells[20 + ind, 9] = DateTime.Parse(dtOuter.Rows[0]["审核时间"].ToString()).ToString("D"); mysheet.Cells[22 + ind, 7] = dtOuter.Rows[0]["批准人"].ToString(); mysheet.Cells[22 + ind, 9] = DateTime.Parse(dtOuter.Rows[0]["批准时间"].ToString()).ToString("D"); }
public bool Export(DataGridView dgv) { //定义Excel操作对象 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); //定义Excel工作表 Microsoft.Office.Interop.Excel.Worksheet workSheet = excelApp.Workbooks.Add().Worksheets[1]; //设置标题样式(从第2行,第2列开始) workSheet.Cells[2, 2] = "学生成绩表";//设置标题内容 workSheet.Cells[2, 2].RowHeight = 25; Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range("B2", "H2"); range.Merge(0); //合并表头单元格 range.Borders.Value = 1; //设置表头的边框 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置单元格内容剧中显示 range.Font.Size = 15; //获取总列数和总行数 int colcomCount = dgv.ColumnCount; int rowCount = dgv.RowCount; //显示列标题 for (int i = 0; i < colcomCount; i++) { //从第3行开始 workSheet.Cells[3, i + 2] = dgv.Columns[i].HeaderText; workSheet.Cells[3, i + 2].Borders.Value = 1; workSheet.Cells[3, i + 2].RowHeight = 23; } //显示数据,从第4列,低2列,开始 for (int i = 0; i < rowCount - 1; i++) { for (int n = 0; n < colcomCount; n++) { //从第3行开始 workSheet.Cells[i + 4, n + 2] = dgv.Rows[i + 1].Cells[n].Value; workSheet.Cells[i + 4, n + 2].Borders.Value = 1; workSheet.Cells[i + 4, n + 2].RowHeight = 23; } } //设置列宽和数据一致 workSheet.Columns.AutoFit(); //打印预览 excelApp.Visible = true; excelApp.Sheets.PrintPreview(); //释放对象 excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; return(true); }
/// <summary> /// 写文本 /// </summary> /// <param name="wsheet"></param> /// <param name="title"></param> /// <param name="colcount"></param> private void DrawText(Microsoft.Office.Interop.Excel.Worksheet wsheet, string txt, System.Drawing.Font font, TextAlign align, int colcount) { _rowindex++; //取得整个报表的标题 wsheet.Cells[_rowindex, 1] = txt; //设置整个报表的标题格式 Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, colcount]]; SetFont(range, font); //range.Borders.LineStyle = 1; //边线 range.HorizontalAlignment = (int)align; // Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //合并单元格 range.Merge(range.MergeCells); }
internal void createHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor) { worksheet.Cells[row, col] = htext; workSheet_range = worksheet.get_Range(cell1, cell2); workSheet_range.Merge(mergeColumns); switch (b) { case "YELLOW": workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb(); break; case "GRAY": workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb(); break; case "GAINSBORO": workSheet_range.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); break; case "Turquoise": workSheet_range.Interior.Color = System.Drawing.Color.Turquoise.ToArgb(); break; case "PeachPuff": workSheet_range.Interior.Color = System.Drawing.Color.PeachPuff.ToArgb(); break; default: // workSheet_range.Interior.Color = System.Drawing.Color..ToArgb(); break; } // workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); // workSheet_range.Font.Bold = font; workSheet_range.ColumnWidth = size; if (fcolor.Equals("")) { workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb(); } else { workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb(); } }
private void InsertReportHeaderRow(ExcelWorksheet worksheet, string startCell, string endCell, string value, bool isBold, bool isCenter) { ExcelRange range = worksheet.get_Range(startCell + ":" + endCell); range.Merge(); range.Value = value; range.Font.Bold = isBold; if (isCenter) { ExcelTemplate.SetTopAlignCenter(worksheet, startCell, endCell); } else { ExcelTemplate.SetTopAlignLeft(worksheet, startCell, endCell); } }
public void createHeaders(int inRow, int inColumn, string inHeaderText, string inCell1, string inCell2, int inMergeColumns, string inBackGroundColor, bool inBold, int inSize, string inFontColor, int inFontSize) { workSheet1.Cells[inRow, inColumn] = inHeaderText; workSheetRange = workSheet1.Range[inCell1, inCell2]; workSheetRange.Merge(inMergeColumns); switch (inBackGroundColor) { case "BEIGE": workSheetRange.Interior.Color = System.Drawing.Color.Beige.ToArgb(); break; case "GRAY": workSheetRange.Interior.Color = Color.FromArgb(221, 221, 221); break; case "LIGHTGRAY": workSheetRange.Interior.Color = Color.FromArgb(238, 238, 238); break; case "BLACK": workSheetRange.Interior.Color = Color.FromArgb(0, 0, 0); break; default: break; } workSheetRange.Font.Bold = inBold; workSheetRange.EntireColumn.AutoFit(); workSheetRange.Font.Size = inFontSize; workSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb(); workSheetRange.Font.Name = "Arial"; if (inFontColor.Equals("")) { workSheetRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); } else { workSheetRange.Font.Color = System.Drawing.Color.Black.ToArgb(); } workSheetRange.EntireColumn.AutoFit(); }
public void createHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns,string b, bool font,int size,string fcolor) { worksheet.Cells[row, col] = htext; workSheet_range = worksheet.get_Range(cell1, cell2); workSheet_range.Merge(mergeColumns); switch(b) { case "YELLOW": workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb(); break; case "GRAY": workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb(); break; case "GAINSBORO": workSheet_range.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); break; case "Turquoise": workSheet_range.Interior.Color = System.Drawing.Color.Turquoise.ToArgb(); break; case "PeachPuff": workSheet_range.Interior.Color = System.Drawing.Color.PeachPuff.ToArgb(); break; default: // workSheet_range.Interior.Color = System.Drawing.Color..ToArgb(); break; } workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); workSheet_range.Font.Bold = font; workSheet_range.ColumnWidth = size; if (fcolor.Equals("")) { workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb(); } else { workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb(); } }
public void createHeadersMergeCenterBorder(int inRow, int inColumn, string inHeaderText, string inCell1, string inCell2, int inMergeColumns, string inBackGroundColor, bool inBold, int inSize, string inFontColor, int inFontSize) { workSheet1.Cells[inRow, inColumn] = inHeaderText; workSheetRange = workSheet1.Range[inCell1, inCell2]; workSheetRange.Merge(inMergeColumns); workSheetRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; switch (inBackGroundColor) { case "BEIGE": workSheetRange.Interior.Color = System.Drawing.Color.Beige.ToArgb(); break; case "GRAY": workSheetRange.Interior.Color = Color.FromArgb(221, 221, 221); break; case "LIGHTGRAY": workSheetRange.Interior.Color = Color.FromArgb(238, 238, 238); break; default: break; } workSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb(); workSheetRange.Font.Bold = inBold; workSheetRange.EntireColumn.AutoFit(); workSheetRange.Font.Size = inFontSize; workSheetRange.Font.Name = "Arial"; if (inFontColor.Equals("")) { workSheetRange.Font.Color = System.Drawing.Color.White.ToArgb(); } else { workSheetRange.Font.Color = System.Drawing.Color.Black.ToArgb(); } workSheetRange.EntireColumn.AutoFit(); }
/// <summary> /// 处理固定的4个参数表 /// </summary> /// <param name="dt"></param> /// <param name="name"></param> /// <param name="rowsOffset"></param> /// <param name="colsOffset"></param> private void ParseParameterList(DataTable dt, String name, int rowsOffset, int colsOffset) { range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 8]); range.Select(); range.Merge(false); ws.Cells.set_Item(rowsOffset - 2, colsOffset - 1, "PARAMETERLIST"); ws.Cells.set_Item(rowsOffset - 2, colsOffset, name); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "类型名称"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "长度"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "小数位"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 6, "默认值"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 7, "必输"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 8, "短文本"); if (dt.Rows.Count > 0) { ParseParameterlistToExcel(dt, rowsOffset, colsOffset); } }
public static void CreateHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor) { Worksheet.Cells[row, col] = htext; WorkSheetRange = Worksheet.Range[cell1, cell2]; WorkSheetRange.Merge(mergeColumns); switch (b) { case "YELLOW": WorkSheetRange.Interior.Color = Color.Yellow.ToArgb(); break; case "GRAY": WorkSheetRange.Interior.Color = Color.Gray.ToArgb(); break; case "GAINSBORO": WorkSheetRange.Interior.Color = Color.Gainsboro.ToArgb(); break; case "Turquoise": WorkSheetRange.Interior.Color = Color.Turquoise.ToArgb(); break; case "PeachPuff": WorkSheetRange.Interior.Color = Color.PeachPuff.ToArgb(); break; } WorkSheetRange.Borders.Color = Color.Black.ToArgb(); WorkSheetRange.Font.Bold = font; WorkSheetRange.ColumnWidth = size; WorkSheetRange.Font.Color = fcolor.Equals("") ? Color.White.ToArgb() : Color.Black.ToArgb(); }
/// <summary> /// 写标题 /// </summary> /// <param name="wsheet"></param> /// <param name="title"></param> /// <param name="colcount"></param> private void DrawTitle(Microsoft.Office.Interop.Excel.Worksheet wsheet, int colcount) { if (!IsDrawTitle) { return; } _rowindex++; //加行数 Microsoft.Office.Interop.Excel.Range cellrange = (Microsoft.Office.Interop.Excel.Range)wsheet.Cells[_rowindex, 1]; //cellrange.RowHeight = 31; //行高31 //插入图片 //InsertImage(GetImagePath(), cellrange, wsheet); //取得整个报表的标题 string titletxt = _title; if (_isTitleAppendSheetName) { titletxt += "(" + wsheet.Name + ")"; } wsheet.Cells[_rowindex, 1] = titletxt; //设置整个报表的标题格式 Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, colcount]]; //设置字体 SetFont(range, _titlefont); //range.Borders.LineStyle = 1; //边线框 //下边框线 Microsoft.Office.Interop.Excel.Border border = range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom); //border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlSlantDashDot;// 选择先的类型 border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; //字体剧中 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection; //合并单元格 range.Merge(range.MergeCells); range.WrapText = true; }
/// <summary> /// 打印到excel /// </summary> /// <param name="table"></param> /// <param name="SavePath"></param> /// <param name="yundan"></param> public void Printer(System.Data.DataTable table, string SavePath, string yundan) { //---------------------------------****20170911NPOI重写excel文件生成*****--------------------------------------------- //HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //if (hssfworkbook == null) //{ // throw new Exception("无法创建NPOI"); //} //// 新建一个Excel页签 //ISheet sheet = hssfworkbook.CreateSheet("报关单"); //IRow row = sheet.CreateRow(0); //创建sheet页的第0行(索引从0开始) //row.CreateCell(0, CellType.String).SetCellValue("发票声明书");//创建第0行第0列 ///----------------------------------------------------------------------------------------------------------- //创建一个Excel应用程序对象,如果未创建成功则推出。 Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application(); if (excel1 == null) { throw new Exception("无法创建Excel对象,可能你的电脑未装Excel"); } Microsoft.Office.Interop.Excel.Workbooks workBooks1 = excel1.Workbooks; Microsoft.Office.Interop.Excel.Workbook workBook1 = workBooks1.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook1.Worksheets[1]; //取得sheet1 //worksheet.PageSetup.Zoom = 63; //打印时页面设置,缩放比例 //把DataTable的表头导入到Excel的第一行 Microsoft.Office.Interop.Excel.Range xlsRow100 = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[3000, 20]]; Microsoft.Office.Interop.Excel.Range xlsRow99 = worksheet.Range[worksheet.Cells[4, 8], worksheet.Cells[3000, 8]]; xlsRow99.ColumnWidth = 12; //设置列宽度 xlsRow100.Font.Name = "Arial"; //设置字体 xlsRow100.Font.Bold = true; //加粗显示 xlsRow100.Font.Size = 10; //字体大小 Microsoft.Office.Interop.Excel.Range xlsRow = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 11]]; xlsRow.Merge(true); //合并单元格 xlsRow.Value = "发票声明书"; xlsRow.Font.Name = "Arial"; //设置字体 xlsRow.Font.Size = 18; //字体大小 xlsRow.Font.Bold = true; //加粗显示 xlsRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中 xlsRow.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中 //xlsRow.Interior.Color = "255,255,0"; //设置背景颜色 //xlsRow.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框 //xlsRow.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细 Microsoft.Office.Interop.Excel.Range xlsRow1 = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[2, 11]]; xlsRow1.Merge(true); //合并单元格 xlsRow1.Value = "INVOICE STATEMENT "; xlsRow1.Font.Name = "Arial"; //设置字体 xlsRow1.Font.Size = 18; //字体大小 xlsRow1.Font.Bold = true; //加粗显示 xlsRow1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中 xlsRow1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中 Microsoft.Office.Interop.Excel.Range xlsRow2 = worksheet.Range[worksheet.Cells[4, 2], worksheet.Cells[11, 2]]; xlsRow2.Interior.Color = Color.FromArgb(255, 255, 153); worksheet.Cells[4, 2] = "收件人"; //Excel单元格赋值 worksheet.Cells[5, 2] = "Consignee:"; //Excel单元格赋值 worksheet.Cells[6, 2] = "地址:"; //Excel单元格赋值 worksheet.Cells[10, 2] = "邮编:"; //Excel单元格赋值 worksheet.Cells[7, 2] = "Address:"; //Excel单元格赋值 worksheet.Cells[11, 2] = "Zip Code"; //Excel单元格赋值 worksheet.Cells[4, 7] = "公司名称"; //Excel单元格赋值 YANG CHEN WEI worksheet.Cells[5, 7] = "Company Name:"; worksheet.Cells[5, 8] = "NICCHU SHOMU K.K"; worksheet.Cells[7, 8] = "O48"; worksheet.Cells[9, 8] = "080-4730-8688"; worksheet.Cells[11, 8] = "JAPAN"; worksheet.Cells[6, 7] = "城市/地区号:"; worksheet.Cells[7, 7] = "Town/Area Code"; worksheet.Cells[8, 7] = "电话/传真:"; worksheet.Cells[9, 7] = "Phone/Fax:"; worksheet.Cells[10, 7] = "州名/国家:"; worksheet.Cells[11, 7] = "State/Country:"; worksheet.Cells[5, 3] = "YANG CHEN WEI"; worksheet.Cells[7, 3] = "1-3-4-2F "; worksheet.Cells[8, 3] = "TODA SHI.SAITAMA "; worksheet.Cells[9, 3] = "KEN.JAPAN"; worksheet.Cells[11, 3] = "335-0016"; xlsRow2.ColumnWidth = 30; xlsRow2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow2.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 xlsRow2.Font.Name = "Arial"; //设置字体 xlsRow2.Font.Bold = true; //加粗显示 Microsoft.Office.Interop.Excel.Range xlsRow3 = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[11, 1]]; xlsRow3.ColumnWidth = 2; //设置列宽度 Microsoft.Office.Interop.Excel.Range xlsRow4 = worksheet.Range[worksheet.Cells[4, 2], worksheet.Cells[4, 11]]; xlsRow4.RowHeight = 25; //设置行高度 Microsoft.Office.Interop.Excel.Range xlsRow5 = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[11, 11]]; xlsRow5.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow5.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 Microsoft.Office.Interop.Excel.Range xlsRow6 = worksheet.Range[worksheet.Cells[4, 7], worksheet.Cells[11, 7]]; xlsRow6.Interior.Color = Color.FromArgb(255, 255, 153); xlsRow6.ColumnWidth = 20; xlsRow6.Font.Name = "Arial"; //设置字体 xlsRow6.Font.Bold = true; //加粗显示 Microsoft.Office.Interop.Excel.Range xlsRow7 = worksheet.Range[worksheet.Cells[13, 2], worksheet.Cells[14, 2]]; xlsRow7.Font.Name = "Arial"; //设置字体 xlsRow7.Font.Bold = true; //加粗显示 xlsRow7.Interior.Color = Color.FromArgb(255, 255, 153); worksheet.Cells[13, 2] = "运单号:"; worksheet.Cells[14, 2] = "Airway Bill No:"; worksheet.Cells[14, 3] = yundan; xlsRow7.ColumnWidth = 30; xlsRow7.RowHeight = 25; Microsoft.Office.Interop.Excel.Range xlsRow8 = worksheet.Range[worksheet.Cells[4, 3], worksheet.Cells[14, 3]]; xlsRow8.ColumnWidth = 18; Microsoft.Office.Interop.Excel.Range xlsRow9 = worksheet.Range[worksheet.Cells[4, 4], worksheet.Cells[11, 4]]; xlsRow9.ColumnWidth = 2; Microsoft.Office.Interop.Excel.Range xlsRow10 = worksheet.Range[worksheet.Cells[4, 6], worksheet.Cells[11, 6]]; xlsRow10.ColumnWidth = 2; worksheet.Cells[16, 2] = "详细的商品名称"; worksheet.Cells[16, 3] = "海关商品编码"; worksheet.Cells[16, 4] = "生产厂商"; worksheet.Cells[16, 6] = "重量"; worksheet.Cells[16, 8] = "体积"; worksheet.Cells[16, 9] = "数量"; worksheet.Cells[16, 10] = "单价"; worksheet.Cells[16, 11] = "报关总价"; Microsoft.Office.Interop.Excel.Range xlsRow11 = worksheet.Range[worksheet.Cells[16, 2], worksheet.Cells[16, 11]]; xlsRow11.Interior.Color = Color.FromArgb(255, 255, 153); xlsRow11.Font.Name = "Arial"; //设置字体 Microsoft.Office.Interop.Excel.Range xlsRow12 = worksheet.Range[worksheet.Cells[16, 4], worksheet.Cells[16, 5]]; xlsRow12.Merge(true); //合并单元格 Microsoft.Office.Interop.Excel.Range xlsRow13 = worksheet.Range[worksheet.Cells[16, 6], worksheet.Cells[16, 7]]; xlsRow13.Merge(true); //合并单元格 xlsRow11.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow11.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细Full Description of Weight xlsRow11.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中 xlsRow11.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中 worksheet.Cells[17, 2] = "Full Description of"; worksheet.Cells[17, 3] = "Harmonised"; worksheet.Cells[17, 7] = "Weight"; worksheet.Cells[17, 8] = "Dimensions"; worksheet.Cells[17, 9] = "No of "; worksheet.Cells[17, 10] = "Unit value "; worksheet.Cells[17, 11] = "Total Value "; worksheet.Cells[18, 2] = "Goods"; worksheet.Cells[18, 3] = "Code (if have)"; //Items Manufacturer worksheet.Cells[18, 9] = "Items"; worksheet.Cells[18, 10] = "(USD$)"; worksheet.Cells[18, 11] = "(USD$)"; worksheet.Cells[17, 4] = "Manufacturer"; Microsoft.Office.Interop.Excel.Range xlsRow14 = worksheet.Range[worksheet.Cells[17, 4], worksheet.Cells[18, 5]]; xlsRow14.Merge(true); //合并单元格 Microsoft.Office.Interop.Excel.Range xlsRow15 = worksheet.Range[worksheet.Cells[17, 4], worksheet.Cells[18, 4]]; xlsRow15.Merge(true); //合并单元格 Microsoft.Office.Interop.Excel.Range xlsRow16 = worksheet.Range[worksheet.Cells[16, 1], worksheet.Cells[18, 11]]; xlsRow16.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow16.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 //---------------------开始插入数据Datatable--------------------------------------------------------------------------- double allprice = 0; for (int i = 0; i < table.Rows.Count; i++) { worksheet.Cells[(19 + i), 2] = table.Rows[i]["bgname"].ToString(); //table.Rows[i]["BName"].ToString(); worksheet.Cells[(19 + i), 3] = table.Rows[i]["bgcode"].ToString(); worksheet.Cells[(19 + i), 9] = table.Rows[i]["count"].ToString(); worksheet.Cells[(19 + i), 10] = table.Rows[i]["money"].ToString(); worksheet.Cells[(19 + i), 11] = "0"; //table.Rows[i]["Totil"].ToString(); allprice = allprice + Convert.ToDouble("100"); //table.Rows[i]["Totil"].ToString() } Microsoft.Office.Interop.Excel.Range xlsRow17 = worksheet.Range[worksheet.Cells[19, 1], worksheet.Cells[18 + table.Rows.Count, 11]]; xlsRow17.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow17.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 xlsRow17.RowHeight = 25; //设置行高度 xlsRow17.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中 xlsRow17.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中 //------------------------------------------------------------------------------------------------------------------------ Microsoft.Office.Interop.Excel.Range xlsRow18 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count, 1], worksheet.Cells[19 + table.Rows.Count, 11]]; xlsRow18.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow18.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 xlsRow18.Interior.Color = Color.FromArgb(255, 255, 153); Microsoft.Office.Interop.Excel.Range xlsRow19 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count, 1], worksheet.Cells[19 + table.Rows.Count, 10]]; xlsRow19.Merge(true); xlsRow19.Value = "Declared Value Terms of Trade"; xlsRow19.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中 xlsRow19.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中 worksheet.Cells[19 + table.Rows.Count, 11] = allprice; //------------------------------------------------------------------------------------------------------------------------- worksheet.Cells[19 + table.Rows.Count + 2, 2] = "本人认为以上提供的资料属实和正确,货物原产地是:"; worksheet.Cells[19 + table.Rows.Count + 2, 6] = "China"; worksheet.Cells[19 + table.Rows.Count + 3, 2] = "I declare that the above information is true and correct to the best of my knowledge and"; worksheet.Cells[19 + table.Rows.Count + 4, 2] = "that the goods are of "; worksheet.Cells[19 + table.Rows.Count + 4, 6] = "origin "; worksheet.Cells[19 + table.Rows.Count + 4, 5] = "China "; //----------------------------------最下面---------------------------------------------------------------------------------------- Microsoft.Office.Interop.Excel.Range xlsRow20 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 1], worksheet.Cells[19 + table.Rows.Count + 12, 11]]; xlsRow20.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框 xlsRow20.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 Town/Area Code: worksheet.Cells[19 + table.Rows.Count + 7, 2] = "发件人"; worksheet.Cells[19 + table.Rows.Count + 8, 2] = "Consignor:"; worksheet.Cells[19 + table.Rows.Count + 9, 2] = "地址:"; worksheet.Cells[19 + table.Rows.Count + 10, 2] = "Address:"; worksheet.Cells[19 + table.Rows.Count + 11, 2] = "州名/国家"; worksheet.Cells[19 + table.Rows.Count + 12, 2] = "State/County"; worksheet.Cells[19 + table.Rows.Count + 7, 7] = "公司名称:"; worksheet.Cells[19 + table.Rows.Count + 8, 7] = "Company Name:"; worksheet.Cells[19 + table.Rows.Count + 9, 7] = "城市/地区号:"; worksheet.Cells[19 + table.Rows.Count + 10, 7] = "Town/Area Code:"; worksheet.Cells[19 + table.Rows.Count + 11, 7] = "电话/传真/电子邮件:"; worksheet.Cells[19 + table.Rows.Count + 12, 7] = "Phone/Fax/E-mail:"; Microsoft.Office.Interop.Excel.Range xlsRow21 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 2], worksheet.Cells[19 + table.Rows.Count + 12, 2]]; xlsRow21.Interior.Color = Color.FromArgb(255, 255, 153); Microsoft.Office.Interop.Excel.Range xlsRow22 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 7], worksheet.Cells[19 + table.Rows.Count + 12, 7]]; xlsRow22.Interior.Color = Color.FromArgb(255, 255, 153); Microsoft.Office.Interop.Excel.Range xlsRow23 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 8], worksheet.Cells[19 + table.Rows.Count + 8, 11]]; xlsRow23.Merge(true); worksheet.Cells[19 + table.Rows.Count + 8, 3] = "XUXU"; worksheet.Cells[19 + table.Rows.Count + 9, 3] = "1049 Jinfan Street Wucheng District,"; worksheet.Cells[19 + table.Rows.Count + 10, 3] = "Jinhua City , Zhejiang Province, China"; worksheet.Cells[19 + table.Rows.Count + 12, 3] = "China"; worksheet.Cells[19 + table.Rows.Count + 8, 8] = "LeCheng Network Technology CO., Ltd"; worksheet.Cells[19 + table.Rows.Count + 10, 8] = "O579"; worksheet.Cells[19 + table.Rows.Count + 12, 8] = "18606881258"; try { //保存Excel workBook1.Saved = true; workBook1.SaveCopyAs(SavePath); } catch (Exception ex) { throw new Exception("导出文件时出错,文件可能正被打开!\n" + ex.Message); //MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.ToString()); } workBook1.Close(); excel1.Visible = true; if (excel1 != null) { excel1.Workbooks.Close(); excel1.Quit(); int generation = System.GC.GetGeneration(excel1); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1); excel1 = null; System.GC.Collect(generation); } }
/// <summary> /// Вывод таблицы в эксель /// </summary> public void OutToExcell(string title, DataView table) { var excelapp = new Microsoft.Office.Interop.Excel.Application(); var workbook = excelapp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet; //Получение названий колонок var ColumnName = new List <string>(); for (int i = 0; i < table.Table.Columns.Count; i++) { ColumnName.Add(table.Table.Columns[i].ToString()); } //Выводим название колонок for (int x = 0; x < ColumnName.Count; x++) { worksheet.Rows[2].Columns[x + 1] = ColumnName[x]; } //заполням ячейки for (int y = 3; y < table.Count + 3; y++) { for (int x = 0; x < ColumnName.Count; x++) { worksheet.Rows[y].Columns[x + 1] = table.Table.Rows[y - 3][ColumnName[x]]; } } // (Титульник над содержимым) Выделяем диапазон ячеек от A1 до числа столбцов из DataView Microsoft.Office.Interop.Excel.Range TitleRange = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ColumnName.Count]).Cells; // Производим объединение TitleRange.Merge(Type.Missing); //Размер текста TitleRange.Cells.Font.Size = 16; //Выравнивание по центру TitleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Задание bold для текста TitleRange.Font.Bold = true; //Задаем название титульника worksheet.Cells[1, 1] = title; //Выделение всех ячеек с данными Microsoft.Office.Interop.Excel.Range ContentRange = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[table.Count + 2, ColumnName.Count]).Cells; //Выставление линий ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //Выставление автоширины ContentRange.EntireColumn.AutoFit(); //Отображаем Excel excelapp.AlertBeforeOverwriting = false; excelapp.Visible = true; }
private void btnReporte_Click(object sender, EventArgs e) { try { BD metodos = new BD(); BD.ObtenerConexion(); tableProductosFiltro = metodos.consultarProductoDetalleReporte(); BD.CerrarConexion(); /* * Productos.id_producto AS ID, Productos.modelo AS MODELO, Tamanos.tamano AS TAMAÑO, * Material.nombre AS MATERIAL, Categoria.nombre AS CATEGORIA, Productos.cantidad AS CANTIDAD, * Tipo.nombre AS TIPO, Productos.precio_publico AS PRECIO_PUBLICO, Productos.precio_frecuente AS PRECIO_FRECUENTE, * Productos.precio_mayorista AS PRECIO_MAYORISTA, Tamanos.descripcion AS DESCRIPCION, Productos.peso AS PESO */ for (int i = 0; i < tableProductosFiltro.Rows.Count; i++) { for (int j = 0; j < tableProductosFiltro.Columns.Count; j++) { Console.Write(tableProductosFiltro.Rows[i][j]); } Console.WriteLine(""); } } catch { txtGenerando.Text = "Error de conexión ..."; } if (tableProductosFiltro.Rows.Count != 0) { try { txtGenerando.Text = "GENERANDO DOCUMENTO ..."; Cursor.Current = Cursors.WaitCursor; SaveFileDialog fichero = new SaveFileDialog(); fichero.FileName = "reporte_inventario_" + Inicio.fecha; fichero.Filter = "Excel (*.xls)|*.xls"; if (fichero.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel.Application aplicacion; Microsoft.Office.Interop.Excel.Workbook libros_trabajo; Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo; Microsoft.Office.Interop.Excel.Range inicio; Microsoft.Office.Interop.Excel.Range ultimo; aplicacion = new Microsoft.Office.Interop.Excel.Application(); libros_trabajo = aplicacion.Workbooks.Add(); hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1); hoja_trabajo.Range["B5:M5"].Font.Bold = true; //hoja_trabajo.Cells[2,8] = "Reporteador"; hoja_trabajo.Cells[6, 3] = "Fecha del reporte: " + Inicio.fecha; aplicacion.get_Range("H2:M4").Merge(true); Microsoft.Office.Interop.Excel.Range titulo = hoja_trabajo.get_Range("H2:M4"); titulo.Merge(); titulo.Value = "INVENTARIO"; //titulo.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //titulo.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; titulo.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; titulo.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; titulo.Font.Size = 36; titulo.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium); titulo.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen; titulo.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhite; /////////////////////////////// // aplicacion.get_Range("H7:M10").Merge(true); Microsoft.Office.Interop.Excel.Range nombre = hoja_trabajo.get_Range("H7:M10"); nombre.Merge(); nombre.Value = "BASES Y MOLDURAS"; nombre.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; nombre.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; nombre.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen; nombre.Font.Size = 30; //////////////////////////// Microsoft.Office.Interop.Excel.Range encabezado = hoja_trabajo.get_Range("B5:T12"); encabezado.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium); encabezado.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhiteSmoke; //CONTENIDO hoja_trabajo.Cells[13, 2] = "Id"; hoja_trabajo.Cells[13, 3] = "Modelo"; hoja_trabajo.Cells[13, 6] = "Tamaño"; hoja_trabajo.Cells[13, 8] = "Material"; hoja_trabajo.Cells[13, 9] = "Categoria"; hoja_trabajo.Cells[13, 11] = "Cantidad"; hoja_trabajo.Cells[13, 12] = "Tipo"; hoja_trabajo.Cells[13, 14] = "P. P"; hoja_trabajo.Cells[13, 15] = "P. F"; hoja_trabajo.Cells[13, 16] = "P. M"; hoja_trabajo.Cells[13, 17] = "Descripción"; hoja_trabajo.Cells[13, 20] = "Peso"; int hi = 14; int hj = 2; for (int i = 0; i < tableProductosFiltro.Rows.Count; i++) { for (int j = 0; j < tableProductosFiltro.Columns.Count; j++) { if (j == 0) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); hj++; } else if (j == 1) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); hj = hj + 3; aplicacion.get_Range("C" + hi.ToString(), "E" + hi.ToString()).Merge(true); } else if (j == 2) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); hj = hj + 2; aplicacion.get_Range("F" + hi.ToString(), "G" + hi.ToString()).Merge(true); } else if (j == 3) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); //hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue; hj++; } else if (j == 4) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); //hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue; hj = hj + 2; aplicacion.get_Range("I" + hi.ToString(), "J" + hi.ToString()).Merge(true); } else if (j == 5) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); //hoja_trabajo.Cells[hi, hj].Style.Color = Color.LightBlue; hj++; } else if (j == 6) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); //hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue; hj = hj + 2; aplicacion.get_Range("L" + hi.ToString(), "M" + hi.ToString()).Merge(true); } else if (j == 7 || j == 8 || j == 9) { float num = (float)Convert.ToDouble(tableProductosFiltro.Rows[i][j].ToString()); String numero = string.Format("{0:c2}", num); hoja_trabajo.Cells[hi, hj] = numero; hj++; } else if (j == 10) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); //hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue; hj = hj + 3; aplicacion.get_Range("Q" + hi.ToString(), "S" + hi.ToString()).Merge(true); } else if (j == 11) { hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString(); hj++; } } hj = 2; hi++; } //bordes inicio = hoja_trabajo.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); ultimo = hoja_trabajo.get_Range("B13", inicio); Microsoft.Office.Interop.Excel.Borders bordeTotal = ultimo.Borders; bordeTotal.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; bordeTotal.Weight = 3d; // //bold hoja_trabajo.Range["B13:T13"].Font.Bold = true; hoja_trabajo.Range["B13:T13"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen; hoja_trabajo.Range["C6:E6"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightSkyBlue; aplicacion.get_Range("C13", "E13").Merge(true); aplicacion.get_Range("F13", "G13").Merge(true); aplicacion.get_Range("I13", "J13").Merge(true); aplicacion.get_Range("Q13", "S13").Merge(true); aplicacion.get_Range("L13", "M13").Merge(true); libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); libros_trabajo.Close(true); aplicacion.Quit(); txtGenerando.Text = ""; try { System.Diagnostics.Process.Start(fichero.FileName); } catch { DialogResult pregunta2; pregunta2 = MetroFramework.MetroMessageBox.Show(this, "No se puede abrir el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning); } DialogResult pregunta; pregunta = MetroFramework.MetroMessageBox.Show(this, "\nDocumento generado con exito\n Guardado en: " + fichero.FileName + " ", "Documento", MessageBoxButtons.OK, MessageBoxIcon.Question); Cursor.Current = Cursors.Default; } else { Cursor.Current = Cursors.Default; txtGenerando.Text = ""; } } catch (Exception ex) { DialogResult pregunta; pregunta = MetroFramework.MetroMessageBox.Show(this, "Ya se ha generado este documento", "AVISO" + ex, MessageBoxButtons.OK, MessageBoxIcon.Warning); Console.WriteLine("Error" + ex); Cursor.Current = Cursors.Default; txtGenerando.Text = ""; } } else { DialogResult pregunta; pregunta = MetroFramework.MetroMessageBox.Show(this, "No existen datos para generar el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public string ExcelExport(System.Data.DataTable DT, string title) { try { //创建Excel Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(System.Type.Missing); //创建工作表(即Excel里的子表sheet) 1表示在子表sheet1里进行数据导出 Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1]; //如果数据中存在数字类型 可以让它变文本格式显示 ExcelSheet.Cells.NumberFormat = "@"; //设置工作表名 ExcelSheet.Name = title; //设置Sheet标题 string start = "A1"; string end = ChangeASC(DT.Columns.Count) + "1"; Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end); _Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计) _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end); _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; _Range.Font.Size = 22; //设置字体大小 _Range.Font.Name = "宋体"; //设置字体的种类 ExcelSheet.Cells[1, 1] = title; //Excel单元格赋值 _Range.EntireColumn.AutoFit(); //自动调整列宽 //写表头 for (int m = 1; m <= DT.Columns.Count; m++) { ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString(); start = "A2"; end = ChangeASC(DT.Columns.Count) + "2"; _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end); _Range.Font.Size = 15; //设置字体大小 _Range.Font.Bold = true; //加粗 _Range.Font.Name = "宋体"; //设置字体的种类 _Range.EntireColumn.AutoFit(); //自动调整列宽 _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } //写数据 for (int i = 0; i < DT.Rows.Count; i++) { for (int j = 1; j <= DT.Columns.Count; j++) { //Excel单元格第一个从索引1开始 // if (j == 0) j = 1; ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString(); } } //表格属性设置 for (int n = 0; n < DT.Rows.Count + 1; n++) { start = "A" + (n + 3).ToString(); end = ChangeASC(DT.Columns.Count) + (n + 3).ToString(); //获取Excel多个单元格区域 _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end); _Range.Font.Size = 12; //设置字体大小 _Range.Font.Name = "宋体"; //设置字体的种类 _Range.EntireColumn.AutoFit(); //自动调整列宽 _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式 _Range.EntireColumn.AutoFit(); //自动调整列宽 } ExcelApp.DisplayAlerts = false; //保存Excel的时候,不弹出是否保存的窗口直接进行保存 //弹出保存对话框,并保存文件 Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog(); sfd.DefaultExt = ".xlsx"; sfd.Filter = "Office 2007 File|*.xlsx|Office 2000-2003 File|*.xls|所有文件|*.*"; if (sfd.ShowDialog() == true) { if (sfd.FileName != "") { ExcelBook.SaveAs(sfd.FileName); //将其进行保存到指定的路径 // MessageBox.Show("导出文件已存储为: " + sfd.FileName, "温馨提示"); } } //释放可能还没释放的进程 ExcelBook.Close(); ExcelApp.Quit(); // PubHelper.Instance.KillAllExcel(ExcelApp); return(sfd.FileName); } catch { // MessageBox.Show("导出文件保存失败!", "警告!"); return(null); } }
/// <summary> /// 修改电子表格 /// </summary> public static void ChangeExcel(string fileName, int row, string col) { try { //FrmGress frm = new FrmGress(); //frm.Show(); // DevExpress.XtraEditors.ProgressBarControl gress = frm.progressBarControl1; //gress.Properties.Step = 5; //gress.PerformStep(); object wrap = true; object addin = false; object Indent = 0; object Shrink = false; object Reading = true; object rows = row; object cols = col; Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(fileName); Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets; Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); string first = ""; for (int x = row; x < 1000; x++) { if (ws.get_Range(col + x.ToString(), Type.Missing).Value2 != null) { first = ws.get_Range(col + x.ToString(), Type.Missing).Value2.ToString(); break; } } int start = 1; int end = 1; int temp = 1; string sec = ""; string result = ""; bool stop = false; for (int i = row; i < 1000; i++) { stop = false; if (ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2 != null) { sec = ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2.ToString(); if (first != sec) { result = ws.get_Range(col + Convert.ToString(i), Type.Missing).Value2.ToString(); first = ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2.ToString(); if (end == 1) { start = i + 1; } //end=1; stop = true; } else { end++; //ws.Cells[i + 1, col] = ""; } } else { stop = true; } if (stop && end > 1) { //gress.PerformStep(); end += start - 1; for (int k = start; k <= end; k++) { ws.Cells[k, col] = ""; } Microsoft.Office.Interop.Excel.Range range = ws.get_Range(col + start.ToString(), col + end.ToString()); range.Merge(0); // range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; range.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; range.WrapText = wrap; range.AddIndent = addin; range.IndentLevel = Indent; range.ShrinkToFit = Shrink; //range.ReadingOrder = Microsoft.Office.Interop.Excel.Constants.xlContext; range.MergeCells = Reading; range.Value2 = result; end = 1; start = i + 1; if (ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2 == null) { range.Value2 = sec; // gress.Properties.Step = 100; // gress.PerformStep(); break; } } } ws.Activate(); wb.Save(); // frm.Close(); //ep.Quit(); ep.Visible = true; } catch (Exception e) { MessageBox.Show(e.Message); } }
protected void ImgBtnExport_Click(object sender, ImageClickEventArgs e) { //HttpContext.Current.Response.Clear(); //HttpContext.Current.Response.AddHeader( // "content-disposition", string.Format("attachment; filename={0}", "PurchaseOrder")); //HttpContext.Current.Response.ContentType = "application/ms-excel"; // using (System.IO.StringWriter sw = new System.IO.StringWriter()) // { // using (HtmlTextWriter htw = new HtmlTextWriter(sw)) // { //Create a form to contain the grid //Table table = new Table(); Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Application.Workbooks.Add(Type.Missing); try { dttable = (DataTable)ViewState["ImportExel"]; dttable1 = (DataTable)ViewState["TermConditn"]; #region [For CompanyDetails-Excel] #region [Image] Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "G1"); range.Font.Size = 12; range.Font.Bold = true; range.Locked = true; range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range.RowHeight = 30; range.Merge(true); range.Value2 = imgAntTime.ImageUrl; range.EntireColumn.ColumnWidth = 20; range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; #endregion #region [Company Name] Microsoft.Office.Interop.Excel.Range range1 = ExcelApp.get_Range("A2", "G2"); range1.Font.Size = 12; range1.Font.Bold = true; range1.Locked = true; range1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range1.RowHeight = 65; range1.Merge(true); range1.Value2 = lblCompanyName.Text + "\n" + lblCompanyAddress.Text + "\n" + "Phone No :" + lblPhnNo.Text + "\n" + "Fax No :" + lblFaxNo.Text; range1.EntireColumn.ColumnWidth = 20; range1.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range1.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; #endregion #region [ReportName:PurchaseOrderDetails] Microsoft.Office.Interop.Excel.Range range2 = ExcelApp.get_Range("A3", "G3"); range2.Font.Size = 12; range2.Font.Bold = true; range2.Locked = true; range2.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range2.RowHeight = 15; range2.Merge(true); range2.Value2 = "Purchase Order Details"; range2.EntireColumn.ColumnWidth = 20; range2.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range2.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; #endregion #region [PODetails] Microsoft.Office.Interop.Excel.Range range3 = ExcelApp.get_Range("A4", "D4"); range3.Font.Size = 12; range3.Font.Bold = true; range3.Locked = true; range3.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range3.RowHeight = 30; range3.Merge(true); range3.Value2 = "To :" + "\n" + lblSuplier.Text; range3.EntireColumn.ColumnWidth = 20; #endregion #region [PODetails2] Microsoft.Office.Interop.Excel.Range range4 = ExcelApp.get_Range("E4", "G4"); range4.Font.Size = 12; range4.Font.Bold = true; range4.Locked = true; range4.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range4.RowHeight = 30; range4.Merge(true); range4.Value2 = "PO No :" + lblPono.Text + "\n" + "PO Date:" + lblPODate.Text; range4.EntireColumn.ColumnWidth = 20; #endregion #region [For Adding From Grid To Excel] // Storing header part in Excel for (int i = 1; i < dttable.Columns.Count + 1; i++) { ExcelApp.Cells[5, i] = dttable.Columns[i - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int i = 0; i < dttable.Rows.Count; i++) { for (int j = 0; j < dttable.Columns.Count; j++) { ExcelApp.Cells[i + 6, j + 1] = dttable.Rows[i][j].ToString(); } } #endregion #region [ReportName:Terms And Conditions] Cnt1 = dttable.Rows.Count + 7; Cnt2 = Convert.ToInt32(dttable.Rows.Count) - 1; ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1] = "Terms And Condition"; ExcelApp.get_Range(ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1], ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1]).Font.Bold = true; #endregion #region [For Adding From Grid To Excel] if (ViewState["TermConditn"] != null) { if (dttable1.Rows.Count > 0) { // Storing header part in Excel for (int i = 1; i < dttable1.Columns.Count + 1; i++) { if (i == 2) { Microsoft.Office.Interop.Excel.Range Headr = ExcelApp.get_Range(ExcelApp.Cells[dttable.Rows.Count + 7, 2], ExcelApp.Cells[dttable.Rows.Count + 7, 8]); Headr.RowHeight = 30; Headr.Merge(true); Headr.Value2 = dttable1.Columns[i - 1].ColumnName; } else { ExcelApp.Cells[dttable.Rows.Count + 7, i] = dttable1.Columns[i - 1].ColumnName; } } // Storing Each row and column value to excel sheet for (int i = 0; i < dttable1.Rows.Count; i++) { for (int j = 0; j < dttable1.Columns.Count; j++) { if (j == 1) { Microsoft.Office.Interop.Excel.Range TermsData = ExcelApp.get_Range(ExcelApp.Cells[dttable.Rows.Count + 8, 2], ExcelApp.Cells[dttable.Rows.Count + 8, 7]); TermsData.RowHeight = 140; TermsData.Merge(true); TermsData.Value2 = dttable1.Rows[i][j].ToString(); } else { //Microsoft.Office.Interop.Excel.Range Term = ExcelApp.get_Range(ExcelApp.Cells[i + dttable.Rows.Count + 8, j + 1],ExcelApp.Cells[i + dttable.Rows.Count + 8, j ]); ExcelApp.Cells[i + dttable.Rows.Count + 8, j + 1] = dttable1.Rows[i][j].ToString(); //Term.RowHeight = 140; //Term.Value2 = dttable1.Rows[i][j].ToString(); } } } } } else { ExcelApp.Cells[dttable.Rows.Count + 7, 16] = "No Terms And Conditons"; } #endregion //ExcelApp.ActiveWorkbook.SaveCopyAs(@"E:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls"); // Obj_Comm.Export("Purchase Order" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xls", ExcelApp); //HttpContext.Current.Response.Write(sw.ToString()); //HttpContext.Current.Response.End(); //ExcelApp.ActiveWorkbook.Saved = true; //ExcelApp.Quit(); // } //} //******** // render the table into the htmlwriter //sw.Write(ExcelApp); //htw.Write(ExcelApp); //HttpContext.Current.Response.Write(sw.ToString()); //HttpContext.Current.Response.End(); ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); #endregion } catch (ThreadAbortException tex) { } catch (Exception ex) { throw new Exception(ex.Message); } //} //} }
private void excelMergeCells(Microsoft.Office.Interop.Excel.Range excelcells, Microsoft.Office.Interop.Excel.Worksheet excelworksheet, String cellLeftTop, String cellRightBottom) { excelcells = excelworksheet.get_Range(cellLeftTop, cellRightBottom); //Объединяем ячейки excelcells.Merge(Type.Missing); }
//打印功能 private Microsoft.Office.Interop.Excel._Worksheet printValue(Microsoft.Office.Interop.Excel._Worksheet mysheet, Microsoft.Office.Interop.Excel._Workbook mybook) { int ind = 0; //内表信息 int rownum = dt记录详情.Rows.Count; if (rownum > 14) { //在第6行插入 for (int i = 0; i < rownum - 14; i++) { Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mysheet.Rows[19, Type.Missing]; range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); Microsoft.Office.Interop.Excel.Range range1 = mysheet.get_Range("C19"); range1.Merge(mysheet.get_Range("D19")); range1.Merge(mysheet.get_Range("E19")); //mysheet.Cells[19, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//水平居中 //mysheet.Cells[19, 3].Font.Name = "宋体";//字体大小 //mysheet.Cells[19, 3].Font.Size = 10.5;//字体大小 } ind = rownum - 14; } for (int i = 0; i < rownum; i++) { mysheet.Cells[5 + i, 1].Value = dt记录详情.Rows[i]["序号"].ToString(); mysheet.Cells[5 + i, 2].Value = dt记录详情.Rows[i]["确认项目"].ToString(); mysheet.Cells[5 + i, 3].Value = dt记录详情.Rows[i]["确认内容"].ToString(); mysheet.Cells[5 + i, 6].Value = dt记录详情.Rows[i]["确认结果"].ToString() == "Yes" ? "√" : "×"; } //外表信息 mysheet.Cells[3, 1].Value = " 生产指令编号: " + mySystem.Parameter.csbagInstruction; mysheet.Cells[3, 5].Value = " 生产日期: " + mySystem.Parameter.csbagInstruction; string temp = dt记录.Rows[0]["班次"].ToString() == "白班" ? "生产班次: 白班☑ 夜班□" : "生产班次: 白班□ 夜班☑"; mysheet.Cells[3, 6].Value = temp; mysheet.Cells[19 + ind, 1].Value = " 备注: " + dt记录.Rows[0]["备注"].ToString(); String stringtemp = ""; stringtemp = "确认人:" + dt记录.Rows[0]["操作员"].ToString(); stringtemp = stringtemp + " 确认日期:" + Convert.ToDateTime(dt记录.Rows[0]["操作日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dt记录.Rows[0]["操作日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dt记录.Rows[0]["操作日期"].ToString()).Day.ToString() + "日"; mysheet.Cells[20 + ind, 1].Value = stringtemp; stringtemp = "复核人:" + dt记录.Rows[0]["审核员"].ToString(); stringtemp = stringtemp + " 复核日期:" + Convert.ToDateTime(dt记录.Rows[0]["审核日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dt记录.Rows[0]["审核日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dt记录.Rows[0]["审核日期"].ToString()).Day.ToString() + "日"; mysheet.Cells[20 + ind, 5].Value = stringtemp; //加页脚 int sheetnum; SqlDataAdapter da = new SqlDataAdapter("select ID from " + table + " where 生产指令ID=" + InstruID.ToString(), mySystem.Parameter.conn); DataTable dt = new DataTable("temp"); da.Fill(dt); List <Int32> sheetList = new List <Int32>(); for (int i = 0; i < dt.Rows.Count; i++) { sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString())); } sheetnum = sheetList.IndexOf(Convert.ToInt32(dt记录.Rows[0]["ID"])) + 1; //读取ID对应的生产指令编码 SqlCommand comm生产指令编码 = new SqlCommand(); comm生产指令编码.Connection = mySystem.Parameter.conn; comm生产指令编码.CommandText = "select * from 生产指令 where ID= @name"; comm生产指令编码.Parameters.AddWithValue("@name", InstruID); SqlDataReader myReader生产指令编码 = comm生产指令编码.ExecuteReader(); while (myReader生产指令编码.Read()) { Instruction = myReader生产指令编码["生产指令编号"].ToString(); //List<String> list班次 = new List<string>(); //list班次.Add(myReader班次["班次"].ToString()); } myReader生产指令编码.Close(); comm生产指令编码.Dispose(); mysheet.PageSetup.RightFooter = Instruction + "-02-" + sheetnum.ToString("D3") + " &P/" + mybook.ActiveSheet.PageSetup.Pages.Count.ToString(); // "生产指令-步骤序号- 表序号 /&P"; // &P 是页码 //返回 return(mysheet); }
/// <summary> /// 处理固定的4个参数表 /// </summary> /// <param name="dt"></param> /// <param name="name"></param> /// <param name="rowsOffset"></param> /// <param name="colsOffset"></param> private void ParseParameterList(DataTable dt, String name, int rowsOffset, int colsOffset) { range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 8]); range.Select(); range.Merge(false); ws.Cells.set_Item(rowsOffset - 2, colsOffset - 1, "PARAMETERLIST"); ws.Cells.set_Item(rowsOffset - 2, colsOffset, name); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "类型名称"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "长度"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "小数位"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 6, "默认值"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 7, "必输"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 8, "短文本"); if (dt.Rows.Count > 0) { ParseParameterlistToExcel(dt, rowsOffset, colsOffset); } }
protected void ImgBtnExport_Click(object sender, ImageClickEventArgs e) { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Application.Workbooks.Add(Type.Missing); try { dttable = (DataTable)ViewState["ImportExel"]; #region [For CompanyDetails-Excel] #region [Image] Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "K1"); range.Font.Size = 12; range.Font.Bold = true; range.Locked = true; range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range.RowHeight = 30; range.Merge(true); range.Value2 = imgAntTime.ImageUrl; range.EntireColumn.ColumnWidth = 20; range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //range.CopyPicture( //string path = string.Format("http://*****:*****@"D:\AntLOGO", Microsoft.Office.Core.MsoTriState.msoFalse, //Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 100, 100); //ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls"); ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); } catch (ThreadAbortException tex) { } catch (Exception ex) { throw new Exception(ex.Message); } }
protected void ImgBtnExport_Click1(object sender, ImageClickEventArgs e) { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Application.Workbooks.Add(Type.Missing); try { dttable = (DataTable)ViewState["ImportExel"]; #region [For RequisitionDetails-Excel] #region [Image] Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "I1"); range.Font.Size = 12; range.Font.Bold = true; range.Locked = true; range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range.RowHeight = 30; range.Merge(true); range.Value2 = imgAntTime.ImageUrl; range.EntireColumn.ColumnWidth = 20; range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //range.CopyPicture( //string path = string.Format("http://*****:*****@"D:\AntLOGO", Microsoft.Office.Core.MsoTriState.msoFalse, //Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 100, 100); ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); #endregion } catch (ThreadAbortException tex) { } catch (Exception ex) { throw new Exception(ex.Message); } }
//保存学生数据到指定Excel文件中 private bool saveFile(string fileName) { bool result = false;//默认保存失败 try { //创建Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); object missing = System.Reflection.Missing.Value;//获取缺少的object类型值 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workBook = excel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets["Sheet1"]; //workBook.Worksheets.Add(missing, missing, missing, missing); int rowIndex = 1; //excel操作行标号 Microsoft.Office.Interop.Excel.Range contentRange1 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]]; contentRange1.Merge(); //合并单元格 contentRange1.RowHeight = 1; rowIndex = rowIndex + 1; //第二行标号 Microsoft.Office.Interop.Excel.Range contentRange2 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]]; contentRange2.Merge(); //合并单元格 contentRange2.RowHeight = 40; contentRange2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示 contentRange2.Font.Bold = true; contentRange2.Font.Name = "宋体"; contentRange2.Font.Size = 18; contentRange2.NumberFormat = "@";//文本格式 workSheet.Cells[rowIndex, 1] = this.titleInformation.Xxmc + " " + this.titleInformation.Xymc + " " + this.titleInformation.Zymc + "人员基本信息表"; //contentRange2.UseStandardHeight = 5; rowIndex = rowIndex + 1; //第三行标号 Microsoft.Office.Interop.Excel.Range contentRange3 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]]; contentRange3.RowHeight = 30; //设置行高 contentRange3.NumberFormat = "@"; //文本格式 Microsoft.Office.Interop.Excel.Range contentRange31 = excel.Range[workSheet.Cells[rowIndex, 2], workSheet.Cells[rowIndex, 3]]; contentRange31.Merge(); workSheet.Cells[rowIndex, 1] = "班级:"; workSheet.Cells[rowIndex, 2] = this.titleInformation.Bjmc; workSheet.Cells[rowIndex, 4] = "填表人:"; workSheet.Cells[rowIndex, 5] = this.titleInformation.Tbrmc; workSheet.Cells[rowIndex, 7] = "联系方式:"; workSheet.Cells[rowIndex, 8] = this.titleInformation.Lxdh; workSheet.Cells[rowIndex, 10] = "填表日期:"; workSheet.Cells[rowIndex, 11] = this.titleInformation.Tbrq.ToString("yyyy/MM/dd"); rowIndex = rowIndex + 1;//第四行标号 Microsoft.Office.Interop.Excel.Range contentRange4 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]]; contentRange4.Worksheet.StandardWidth = 15; contentRange4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; contentRange4.Borders.LineStyle = 1; //设置边框格式 contentRange4.NumberFormat = "@"; //文本格式 workSheet.Cells[rowIndex, 1] = "姓名"; workSheet.Cells[rowIndex, 2] = "身份证号"; workSheet.Cells[rowIndex, 3] = "性别"; workSheet.Cells[rowIndex, 4] = "民族"; workSheet.Cells[rowIndex, 5] = "文化程度"; workSheet.Cells[rowIndex, 6] = "政治面貌"; workSheet.Cells[rowIndex, 7] = "宗教信仰"; workSheet.Cells[rowIndex, 8] = "户籍地代码"; workSheet.Cells[rowIndex, 9] = "户籍地详细地址"; workSheet.Cells[rowIndex, 10] = "户口性质"; workSheet.Cells[rowIndex, 11] = "备注"; rowIndex = rowIndex + 1;//第五行标号开始进行数据录入 foreach (StudentInfo stuItem in this.StuList) { Microsoft.Office.Interop.Excel.Range contentRange = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]]; contentRange.Worksheet.StandardWidth = 15; contentRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; contentRange.NumberFormat = "@"; //文本格式 contentRange.Borders.LineStyle = 1; //设置边框格式 workSheet.Cells[rowIndex, 1] = stuItem.Xm; workSheet.Cells[rowIndex, 2] = stuItem.Zjhm; workSheet.Cells[rowIndex, 3] = stuItem.Xb; workSheet.Cells[rowIndex, 4] = stuItem.Mz; workSheet.Cells[rowIndex, 5] = stuItem.Whcd; workSheet.Cells[rowIndex, 6] = stuItem.Zzmm; workSheet.Cells[rowIndex, 7] = stuItem.Zjxy; workSheet.Cells[rowIndex, 8] = stuItem.Hjdxzqh; workSheet.Cells[rowIndex, 9] = stuItem.Hjdxz; workSheet.Cells[rowIndex, 10] = stuItem.Hkxz; workSheet.Cells[rowIndex, 11] = stuItem.Bzxx; rowIndex++; //换下一行 } excel.Application.DisplayAlerts = false; //不显示提示对话框 workBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn8); //保存工作表:xlAddIn8设置输出格式为xls workBook.Close(false, missing, missing); //获取并关闭Excel相关进程 System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL"); foreach (System.Diagnostics.Process p in excelProcess) { p.Kill(); } result = true;//标志正常写入完成 } catch (Exception e) { Console.WriteLine(e.Message); result = false; } return(result); }
private void ExportExcel(string fileName, DataGridView myDGV) { string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) { return; //被点了取消 } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Microsoft.Office.Interop.Excel.Range range = null; Microsoft.Office.Interop.Excel.Range range1 = worksheet.get_Range("B2", "W3"); range1.Select(); range1.Merge(); range1.Font.Size = 15; range1.Borders.LineStyle = 1; range1.Value2 = "昌 吉 州 人 民 医 院 手 术 通 知 单"; range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Microsoft.Office.Interop.Excel.Range range2 = worksheet.get_Range("B4", "W4"); range2.Select(); range2.Merge(); range2.Font.Size = 11; range2.Value2 = " 手术日期:" + dtDataTime.Text; range2.Borders.LineStyle = 1; Microsoft.Office.Interop.Excel.Range excelRange = worksheet.get_Range("A6", "W6"); excelRange.Select(); xlApp.ActiveWindow.FreezePanes = true; //写入标题 //int ColCount = 0; for (int i = 0; i < myDGV.ColumnCount; i++) { worksheet.Cells[5, i + 2] = myDGV.Columns[i].HeaderText; range = xlApp.Cells[5, i + 2]; range.Font.Bold = true; range.RowHeight = 25; range.Interior.ColorIndex = 34; range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range.Borders.LineStyle = 1; if (i == 3 || i == 8 || i == 9) { range.ColumnWidth = 10; } else { range.EntireColumn.AutoFit(); } } //写入数值 for (int r = 0; r < myDGV.Rows.Count; r++) { for (int i = 0; i < myDGV.ColumnCount; i++) { worksheet.Cells[r + 6, i + 2] = myDGV.Rows[r].Cells[i].Value; range = worksheet.Cells[r + 6, i + 2]; range.Font.Size = 9; range.WrapText = true; int[] a = { 1, 1, 2, 4, 5, 6, 7, 8, 9 }; foreach (int dr in a) { if (i == dr) { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } else { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } } range.EntireRow.AutoFit();//行高自适应 range.Borders.LineStyle = 1; } System.Windows.Forms.Application.DoEvents(); } //worksheet.Columns.EntireColumn.Width = 40;//列宽自适应 //worksheet.Rows.AutoFilter(); if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); ProgressBar pbar = new ProgressBar(); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } xlApp.Quit(); GC.Collect();//强行销毁 MessageBox.Show("文件: " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public static void CreateHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor) { Worksheet.Cells[row, col] = htext; WorkSheetRange = Worksheet.Range[cell1, cell2]; WorkSheetRange.Merge(mergeColumns); switch (b) { case "YELLOW": WorkSheetRange.Interior.Color = Color.Yellow.ToArgb(); break; case "GRAY": WorkSheetRange.Interior.Color = Color.Gray.ToArgb(); break; case "GAINSBORO": WorkSheetRange.Interior.Color = Color.Gainsboro.ToArgb(); break; case "Turquoise": WorkSheetRange.Interior.Color = Color.Turquoise.ToArgb(); break; case "PeachPuff": WorkSheetRange.Interior.Color = Color.PeachPuff.ToArgb(); break; } WorkSheetRange.Borders.Color = Color.Black.ToArgb(); WorkSheetRange.Font.Bold = font; WorkSheetRange.ColumnWidth = size; WorkSheetRange.Font.Color = fcolor.Equals("") ? Color.White.ToArgb() : Color.Black.ToArgb(); }
public void OutPut2(DataTable dt) { bool isShowExcel = false; if (dt == null) { return; } if (dt.Rows.Count == 0) { //return; } Missing miss = Missing.Value; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("请确保您的电脑已经安装Excel!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //xlApp.UserControl = true; Microsoft.Office.Interop.Excel.Workbooks workBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workBook = null; if (!File.Exists(base.m_TemplateFilePath)) { workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//创建新的 } else { workBook = workBooks.Add(base.m_TemplateFilePath); //根据现有excel模板产生新的Workbook } Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1]; //获取sheet1 xlApp.DisplayAlerts = false; //保存Excel的时候,不弹出是否保存的窗口直接进行保存 //workSheet.get_Range("A3", "B3").Merge(workSheet.get_Range("A3", "B3").MergeCells);//合并单元格 if (workSheet == null) { MessageBox.Show("请确保您的电脑已经安装Excel!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } try { //Microsoft.Office.Interop.Excel.Range range = null; xlApp.Visible = isShowExcel;//若是true,则在导出的时候会显示excel界面 int totalCount = dt.Rows.Count; if (File.Exists(base.m_TemplateFilePath)) { for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { for (int k = 0; k < workBook.Names.Count; k++) { if (workBook.Names.Item(dt.Columns[j].Caption) != null)//.Name == dt.Columns[j].Caption) { //定义第一个全局命名区域 int row = workBook.Names.Item(dt.Columns[j].Caption).RefersToRange.Row; int col = workBook.Names.Item(dt.Columns[j].Caption).RefersToRange.Column; workSheet.Cells[row + i + 1, col] = "'" + dt.Rows[i][j].ToString(); break; } } } } } else { if (MessageBox.Show("没有找到数据表格模板,是否按默认的格式导出?", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No) { return; } workSheet.Cells[1, 1] = dt.TableName;//导出标题 for (int j = 0; j < dt.Columns.Count; j++) { workSheet.Cells[4, j + 1] = dt.Columns[j].ColumnName; workBook.Names.Add(dt.Columns[j].ColumnName, workSheet.Cells[4, j + 1]);//, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //workBook.Names.Item(dt.Columns[j].ColumnName).RefersTo = workSheet.get_Range(workSheet.Cells[4, j + 1]); } Microsoft.Office.Interop.Excel.Range newExpenseTypeRange = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[3, dt.Columns.Count]); newExpenseTypeRange.Merge(workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[3, dt.Columns.Count]).MergeCells);//合并单元格 newExpenseTypeRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; newExpenseTypeRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //写入数值 for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Rows[i][j].GetType().Equals(typeof(DateTime))) { workSheet.Cells[i + 5, j + 1] = ((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss"); } else { workSheet.Cells[i + 5, j + 1] = dt.Rows[i][j];//项目序号 } } } WorkSheetPageSet(xlApp, workSheet); } string fileExt = DateTime.Now.ToString("yyyyMMddHHmmss"); string fileOutPut = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt); workBook.SaveAs(fileOutPut, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //workSheet.SaveAs(base.templateFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workBooks.Close(); if (MessageBox.Show("Excel导出成功:" + fileOutPut + "\r\n是否要打开?", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { System.Diagnostics.Process.Start(fileOutPut); } } catch (Exception ex) { MessageBox.Show("Excel导出失败,错误:" + ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); xlApp.Quit(); } finally { xlApp.Quit(); } }///// <summary>
public void ExcelExport(System.Data.DataTable dt, string SheetName) { Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application(); SaveFileDialog savefiledialog = new SaveFileDialog(); System.Reflection.Missing miss = System.Reflection.Missing.Value; appexcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbookdata = appexcel.Workbooks.Add(System.Type.Missing); Microsoft.Office.Interop.Excel.Worksheet worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets[1]; //Microsoft.Office.Interop.Excel.Range rangedata; //创建Excel //Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Workbook ExcelBook = appexcel.Workbooks.Add(System.Type.Missing); ////创建工作表(即Excel里的子表sheet) 1表示在子表sheet1里进行数据导出 //Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1]; ////设置Sheet标题 string start = "A1"; string end = ChangeASC(dt.Columns.Count) + "1"; Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)worksheetdata.get_Range(start, end); _Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计) _Range = worksheetdata.get_Range(start, end); _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; _Range.Font.Size = 22; //设置字体大小 _Range.Font.Name = "宋体"; //设置字体的种类 worksheetdata.Cells[1, 1] = SheetName; //Excel单元格赋值 _Range.EntireColumn.AutoFit(); //自动调整列宽 //设置对象不可见 appexcel.Visible = false; System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us"); //workbookdata = appexcel.Workbooks.Add(miss); //worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss); //给工作表赋名称 worksheetdata.Name = SheetName; start = "A2"; end = ChangeASC(dt.Columns.Count) + "2"; _Range = worksheetdata.get_Range(start, end); _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; for (int i = 0; i < dt.Columns.Count; i++) { worksheetdata.Cells[2, i + 1] = dt.Columns[i].ColumnName.ToString(); } //因为第一行已经写了表头,所以所有数据都应该从a2开始 //rangedata = worksheetdata.get_Range("a3", miss); //Microsoft.Office.Interop.Excel.Range xlrang = null; //irowcount为实际行数,最大行 int irowcount = dt.Rows.Count; int iparstedrow = 1, icurrsize = 0; //ieachsize为每次写行的数值,可以自己设置 int ieachsize = 1000; //icolumnaccount为实际列数,最大列数 int icolumnaccount = dt.Columns.Count; //在内存中声明一个ieachsize×icolumnaccount的数组,ieachsize是每次最大存储的行数,icolumnaccount就是存储的实际列数 object[,] objval = new object[ieachsize + 1, icolumnaccount]; icurrsize = ieachsize; while (iparstedrow <= irowcount) { if ((irowcount - iparstedrow) < ieachsize) { icurrsize = irowcount - iparstedrow + 1; } //用for循环给数组赋值 for (int i = 0; i < icurrsize; i++) { for (int j = 0; j < icolumnaccount; j++) { objval[i, j] = dt.Rows[i + iparstedrow - 1][j].ToString(); } //System.Windows.Forms.Application.DoEvents(); } string X = "A" + ((int)(iparstedrow + 2)).ToString(); string col = ""; if (icolumnaccount <= 26) { col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString(); } else { col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString(); } _Range = worksheetdata.get_Range(X, col); // 调用range的value2属性,把内存中的值赋给excel _Range.Value2 = objval; _Range.EntireColumn.AutoFit(); //自动调整列宽 _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; iparstedrow = iparstedrow + icurrsize; } //保存工作表 System.Runtime.InteropServices.Marshal.ReleaseComObject(_Range); _Range = null; //调用方法关闭excel进程 appexcel.Visible = true; }
protected virtual void Merge(int col1, int row1, int col2, int row2) { _excelRange = _excelSheet.get_Range(_excelSheet.Cells[col1, row1], _excelSheet.Cells[col1, row2]); _excelRange.Merge(true); }
/// <summary> /// 如果字段的类型是结构体,则需要分开处理 /// </summary> /// <param name="dt"></param> /// <param name="rowsOffset"></param> /// <param name="colsOffset"></param> private void ParseStructToExcel(DataTable dt, ref int rowsOffset, int colsOffset) { foreach (DataRow row in dt.Rows) { if (row["DataType"].ToString() == "STRUCTURE") { String structurName = row["DataTypeName"].ToString(); String Documentation = row[FuncFieldText.DOCUMENTATION].ToString(); if (_parsedStructure.Contains(structurName)) { return; } DataTable dt2 = m_function.FunctionMeta.StructureDetail[structurName]; if (dt2 != null) { if (dt2.Rows.Count > 0) { _parsedStructure.Add(structurName); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]); range.Select(); range.Merge(false); ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "STRUCTURE"); ws.Cells.set_Item(rowsOffset - 4, colsOffset, "结构"); ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称"); ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注"); ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, structurName); ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本"); ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset); rowsOffset += dt2.Rows.Count + 7; } } } else if (row["DataType"].ToString() == "TABLE") { String tableName = row["DataTypeName"].ToString(); String Documentation = row[FuncFieldText.DOCUMENTATION].ToString(); if (_parsedTable.Contains(tableName)) { return; } DataTable dt2 = m_function.FunctionMeta.StructureDetail[tableName]; if (dt2 != null) { if (dt2.Rows.Count > 0) { _parsedTable.Add(tableName); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]); range.Select(); range.Merge(false); ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "TABLE"); ws.Cells.set_Item(rowsOffset - 4, colsOffset, "表"); ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称"); ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注"); ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, tableName); ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本"); ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset); rowsOffset += dt2.Rows.Count + 7; } } } } }
/// <summary> /// 如果字段的类型是结构体,则需要分开处理 /// </summary> /// <param name="dt"></param> /// <param name="rowsOffset"></param> /// <param name="colsOffset"></param> private void ParseStructToExcel(DataTable dt, ref int rowsOffset, int colsOffset) { foreach (DataRow row in dt.Rows) { if (row["DataType"].ToString() == "STRUCTURE") { String structurName = row["DataTypeName"].ToString(); String Documentation = row[FuncFieldText.DOCUMENTATION].ToString(); if (_parsedStructure.Contains(structurName)) { return; } DataTable dt2 = m_function.FunctionMeta.StructureDetail[structurName]; if (dt2 != null) { if (dt2.Rows.Count > 0) { _parsedStructure.Add(structurName); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]); range.Select(); range.Merge(false); ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "STRUCTURE"); ws.Cells.set_Item(rowsOffset - 4, colsOffset, "结构"); ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称"); ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注"); ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, structurName); ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本"); ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset); rowsOffset += dt2.Rows.Count + 7; } } } else if (row["DataType"].ToString() == "TABLE") { String tableName = row["DataTypeName"].ToString(); String Documentation = row[FuncFieldText.DOCUMENTATION].ToString(); if (_parsedTable.Contains(tableName)) { return; } DataTable dt2 = m_function.FunctionMeta.StructureDetail[tableName]; if (dt2 != null) { if (dt2.Rows.Count > 0) { _parsedTable.Add(tableName); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]); range.Select(); range.Merge(false); range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]); range.Select(); range.Merge(false); ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "TABLE"); ws.Cells.set_Item(rowsOffset - 4, colsOffset, "表"); ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称"); ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注"); ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, tableName); ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位"); ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本"); ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset); rowsOffset += dt2.Rows.Count + 7; } } } } }
/// <summary> /// 合并单元格并填写值 /// </summary> /// <param name="worksheet"></param> /// <param name="value">单元格的值</param> /// <param name="sRow">开始行</param> /// <param name="sCol">开始列</param> /// <param name="endRow">结束行</param> /// <param name="endCol">结束列</param> public static void MergeCells(this Microsoft.Office.Interop.Excel.Worksheet worksheet, object value, int sRow, int sCol, int endRow, int endCol) { Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[sRow, sCol], worksheet.Cells[endRow, endCol]); range.Merge(); range.Value = value; }