private void button1_Click(object sender, EventArgs e) { // 获取数据库数据 DataTable dataTable = new DataTable(); string saveFileName = "拌合站Excel"; /*新建表;新建Sheet并命名;设定cellStyle*/ XSSFWorkbook book = new XSSFWorkbook(); ISheet sheet1 = book.CreateSheet("Sheet1"); IRow headerRow4Sheet1 = sheet1.CreateRow(0); ExcelCellStyle cellStyle = new ExcelCellStyle(book); ICell cell; try { if ("sqlserver".Equals(dbType.Text)) { DBHelp db = new DBHelp(); db.Url = @"Data Source=" + url.Text + "," + port.Text + ";Initial Catalog=" + dbName.Text + ";User ID=" + username.Text + ";pwd=" + password.Text + ""; dataTable = db.adapterFind(sql.Text); } if ("mysql".Equals(dbType.Text)) { MySqlUtil db = new MySqlUtil(); // db.Url = @"server=" + url.Text + ";port=" + port.Text + ";user="******";password="******"; database=" + dbName.Text + ";"; // db.Url = @"data source=" + url.Text + ";database=" + dbName.Text + ";user id=" + username.Text + ";password="******";pooling=false;charset=utf8;"; db.Url = @"Server=" + url.Text + ";port=" + port.Text + ";Database=" + dbName.Text + ";Uid=" + username.Text + ";Pwd=" + password.Text + ";CharSet=utf8;"; dataTable = db.adapterFind(sql.Text); } if ("oracle".Equals(dbType.Text)) { OracleUtil db = new OracleUtil(); db.Url = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + url.Text + ")(PORT=" + port.Text + ")))(CONNECT_DATA=(SERVICE_NAME=" + dbName.Text + ")));User ID=" + username.Text + ";Password="******";"; dataTable = db.adapterFind(sql.Text); } /*设定标题行*/ int columnNum = 0; columnNum = dataTable.Columns.Count; string[] colNames = new string[columnNum]; for (int i = 0; i < dataTable.Columns.Count; i++) { colNames[i] = dataTable.Columns[i].ColumnName; } for (int i = 0; i < colNames.Length; i++) { cell = headerRow4Sheet1.CreateCell(i); cell.CellStyle = cellStyle.style; cell.SetCellValue(colNames[i]); } /*设定内容行*/ int sheet1RowID = 1;//从表格的第二2行开始进行循环 foreach (DataRow dataRow in dataTable.Rows) { IRow r = sheet1.CreateRow(sheet1RowID); for (int i = 0; i < colNames.Length; i++) { cell = r.CreateCell(i); cell.SetCellValue(dataRow[i].ToString().Trim()); cell.CellStyle = cellStyle.style; } sheet1RowID = sheet1RowID + 1; } /*单元格长度格式化*/ ChangeStyle(book, sheet1); } catch (Exception ex) { cell = headerRow4Sheet1.CreateCell(0); cell.CellStyle = cellStyle.style; cell.SetCellValue(string.Format("异常类型:{0}\r\n异常消息:{1}\r\n异常信息:{2}\r\n", ex.GetType().Name, ex.Message, ex.StackTrace)); saveFileName = "查询出错请检查填写信息"; } /*IO流输出保存*/ SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xlsx"; saveDialog.Filter = "Excel文件|*.xlsx"; saveDialog.FileName = saveFileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; MemoryStream ms = new MemoryStream(); book.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); book.Write(file); file.Close(); book = null; ms.Close(); ms.Dispose(); }