Aspose.Cells.dll的用法
1 public void OutExcel() 2 { 3 #region 4 WorkbookDesigner designer = new WorkbookDesigner(); 5 Worksheet sheet = designer.Workbook.Worksheets[0]; 6 Workbook book = designer.Workbook; 7 8 Aspose.Cells.Style style1 = book.Styles[book.Styles.Add()]; 9 style1.Font.Name = "黑体";//文字字体 10 style1.Font.Size = 16;//文字大小 11 12 Aspose.Cells.Style style2 = book.Styles[book.Styles.Add()]; 13 style2.HorizontalAlignment = TextAlignmentType.Center; 14 style2.Font.Name = "宋体";//文字字体 15 style2.Font.IsBold = true;//粗体 16 style2.Font.Size = 18;//文字大小 17 18 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; 19 style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; 20 style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; 21 style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; 22 style2.Borders[BorderType.TopBorder].Color = Color.Black; 23 style2.Borders[BorderType.BottomBorder].Color = Color.Black; 24 style2.Borders[BorderType.LeftBorder].Color = Color.Black; 25 style2.Borders[BorderType.RightBorder].Color = Color.Black; 26 27 28 29 Aspose.Cells.Style style3 = book.Styles[book.Styles.Add()]; 30 style3.HorizontalAlignment = TextAlignmentType.Center; 31 style3.Font.Name = "宋体";//文字字体 32 style3.Font.Size = 15;//文字大小 33 style2.Font.IsBold = true;//粗体 34 35 36 Aspose.Cells.Style style4 = book.Styles[book.Styles.Add()]; 37 style4.HorizontalAlignment = TextAlignmentType.Center; 38 // style4.Font.Name = "黑体";//文字字体 "Arial Rounded MT Bold";// 39 style4.Font.Size = 11;//文字大小 40 41 Aspose.Cells.Style style5 = book.Styles[book.Styles.Add()]; 42 style5.HorizontalAlignment = TextAlignmentType.Center; 43 style5.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; 44 style5.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; 45 style5.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; 46 style5.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; 47 style5.Borders[BorderType.TopBorder].Color = Color.Black; 48 style5.Borders[BorderType.BottomBorder].Color = Color.Black; 49 style5.Borders[BorderType.LeftBorder].Color = Color.Black; 50 style5.Borders[BorderType.RightBorder].Color = Color.Black; 51 52 //Aspose.Cells.Style style6 = book.Styles[book.Styles.Add()]; 53 //style6.ForegroundColor = Color.FromArgb(153, 204, 0);//设置背景色//#DCE6F1 54 //style6.Pattern = Aspose.Cells.BackgroundType.Solid; 55 56 Aspose.Cells.Style style7 = book.Styles[book.Styles.Add()]; 57 style7.HorizontalAlignment = TextAlignmentType.Center; 58 style7.Font.Name = "黑体";//文字字体 59 style7.Font.Size = 11;//文字大小 60 61 var c21 = sheet.Cells[1, 0]; 62 c21.PutValue("点位"); 63 c21.SetStyle(style1); 64 65 var mm=sheet.Cells[7,1]; 66 mm.PutValue("中国1"); 67 mm.SetStyle(style1); 68 69 var nn = sheet.Cells[7, 2]; 70 nn.PutValue("中国2"); 71 nn.SetStyle(style1); 72 73 Range ranget1 = sheet.Cells.CreateRange(7, 1, 1, 2); 74 ranget1.Merge(); 75 ranget1.RowHeight = 20; 76 77 Cells cells = sheet.Cells; 78 cells.SetColumnWidth(1, 30);//设置列宽 79 80 81 var c22 = sheet.Cells[1, 1]; 82 c22.PutValue("类型"); 83 c22.SetStyle(style1); 84 85 86 for (int i = 0; i < 31; i++) 87 { 88 var c23 = sheet.Cells[1, i + 2]; 89 c23.PutValue(i + 1); 90 91 c23.SetStyle(style1); 92 } 93 sheet.Cells[1, 33].PutValue("最小值"); sheet.Cells[1, 33].SetStyle(style1); 94 sheet.Cells[1, 34].PutValue("最大值"); sheet.Cells[1, 34].SetStyle(style1); 95 sheet.Cells[1, 35].PutValue("平均值"); sheet.Cells[1, 35].SetStyle(style1); 96 97 string year = ""; 98 string month = ""; 99 int rowcount = 0; 100 101 102 103 //添加样式 104 //sheet.Cells[m + t + 1, 33].SetStyle(style4); 105 //sheet.Cells[m + t + 1, 34].SetStyle(style4); 106 //sheet.Cells[m + t + 1, 35].SetStyle(style4); 107 //Range ranget = sheet.Cells.CreateRange(t + k + k, 0, 2, 1);//从第几行第几列到结束行结束列 108 //ranget.Merge(); //合并单元格 109 110 111 Range range5 = sheet.Cells.CreateRange(0, 0, rowcount * 2 + 2, 36); 112 Aspose.Cells.StyleFlag borderStyle = new Aspose.Cells.StyleFlag(); 113 borderStyle.Borders = true; //启用Borders样式 114 borderStyle.HorizontalAlignment = true; //启用水平对齐样式 115 borderStyle.Font = false;//不使用配置的字体样式 116 range5.ApplyStyle(style5, borderStyle); 117 118 119 120 var c11 = sheet.Cells[0, 0]; 121 string BiaoTi = "**监测有限公司" + year + "年" + month + "月水质监测结果汇总表"; 122 c11.PutValue(BiaoTi); 123 c11.SetStyle(style2); 124 125 Range range = sheet.Cells.CreateRange(0, 0, 1, 36); 126 // Cell cell = range[0, 0]; 127 range.Merge(); //合并单元格 128 range.RowHeight = 28; 129 130 Range range2 = sheet.Cells.CreateRange(2, 1, rowcount * 2 + 2, 36); 131 range2.RowHeight = 15;//设置行高 132 133 Range range3 = sheet.Cells.CreateRange(1, 0, 2, 36); 134 range3.RowHeight = 17;//设置行高 135 136 137 string XFilename = HttpUtility.UrlEncode("**环境" + year + "年" + month + "月水质监测汇总表.xls", Encoding.UTF8).ToString(); 138 Aspose.Cells.SaveOptions ss = new XlsSaveOptions(SaveFormat.Excel97To2003); 139 140 designer.Workbook.Save(this.Response, XFilename, ContentDisposition.Attachment, ss); 141 #endregion 142 }
1 public static class SqlHelper 2 { 3 private static readonly string conStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; 4 5 public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms) 6 { 7 using (SqlConnection con = new SqlConnection(conStr)) 8 { 9 using (SqlCommand cmd = new SqlCommand(sql, con)) 10 { 11 cmd.CommandType = cmdType; 12 if (pms != null) 13 { 14 cmd.Parameters.AddRange(pms); 15 } 16 con.Open(); 17 return cmd.ExecuteNonQuery(); 18 } 19 } 20 } 21 22 public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms) 23 { 24 using (SqlConnection con = new SqlConnection(conStr)) 25 { 26 using (SqlCommand cmd = new SqlCommand(sql, con)) 27 { 28 cmd.CommandType = cmdType; 29 if (pms != null) 30 { 31 cmd.Parameters.AddRange(pms); 32 } 33 con.Open(); 34 return cmd.ExecuteScalar(); 35 } 36 } 37 } 38 39 40 public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms) 41 { 42 SqlConnection con = new SqlConnection(conStr); 43 try 44 { 45 using (SqlCommand cmd = new SqlCommand(sql, con)) 46 { 47 cmd.CommandType = cmdType; 48 if (pms != null) 49 { 50 cmd.Parameters.AddRange(pms); 51 } 52 con.Open(); 53 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 54 return reader; 55 } 56 } 57 catch 58 { 59 con.Dispose(); 60 throw; 61 } 62 } 63 64 65 public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms) 66 { 67 using (SqlDataAdapter sda = new SqlDataAdapter(sql, conStr)) 68 { 69 DataTable dt = new DataTable(); 70 71 sda.SelectCommand.CommandType = cmdType; 72 if (pms != null) 73 { 74 sda.SelectCommand.Parameters.AddRange(pms); 75 } 76 sda.Fill(dt); 77 return dt; 78 } 79 } 80 }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。