using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace PDM {
public partial class Export : Form {
public Export() {
InitializeComponent();
this.tbxserver.Text = "192.168.17.216";
this.tbxserverName.Text = "mf_spare_2014_0801";
this.tbxname.Text = "sa";
this.tbxpassword .Text= "sunlight";
}
private void button1_Click(object sender, EventArgs e) {
try {
string strsql;
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = "d:\\";
openFileDialog.Filter = "文本文件|*.*|C#文件|*.cs|所有文件|*.*";
openFileDialog.FilterIndex = 1;
DataSet dss = new DataSet();
SqlConnection oraCon = new SqlConnection(string.Format("server={0};database={1};uid={2};pwd={3}", this.tbxserver.Text, this.tbxserverName.Text,this.tbxname.Text, this.tbxpassword.Text));
oraCon.Open();
// SqlConnection oraCon = new SqlConnection(string.Format("user id={0};data source={1};password={2}", this.tbxname.Text, this.tbxserver.Text, this.tbxpassword.Text));
if(openFileDialog.ShowDialog() == DialogResult.OK) {
var fName = openFileDialog.FileName;
var importtable = XlSToDataTable(fName, "dd", 0);
this.dataGridView1.DataSource = importtable;
foreach(DataRow item in importtable.Rows) {
DataTable dt = new DataTable();
dt.TableName = item[0].ToString();
strsql = (item[1].ToString());
SqlDataAdapter oraDap = new SqlDataAdapter(strsql, oraCon);
oraDap.Fill(dt);
dss.Tables.Add(dt);
}
Stream myStream;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialog1.FileName = "导出数据";
saveFileDialog1.FilterIndex = 1;
if(saveFileDialog1.ShowDialog() == DialogResult.OK) {
GridToExcelByNPOI(dss, saveFileDialog1.FileName);
}
} oraCon.Close();
} catch(Exception ex) {
throw ex;
}
}
private static void GridToExcelByNPOI(DataSet ds, string strExcelFileName) {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
int i = 0;
foreach(DataTable dt in ds.Tables) {
i++;
ISheet sheet = workbook.CreateSheet(dt.TableName);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach(DataColumn item in dt.Columns) {
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
icolIndex++;
}
int iRowIndex = 1;
int iCellIndex = 0;
foreach(DataRow Rowitem in dt.Rows) {
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach(DataColumn Colitem in dt.Columns) {
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
}
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
} catch(Exception ex) {
} finally {
//workbook = null;
}
}
/// <summary>
/// Excel文件导成Datatable
/// </summary>
/// <param name="strFilePath">Excel文件目录地址</param>
/// <param name="strTableName">Datatable表名</param>
/// <param name="iSheetIndex">Excel sheet index</param>
/// <returns></returns>
public static DataTable XlSToDataTable(string strFilePath, string strTableName, int iSheetIndex) {
string strExtName = Path.GetExtension(strFilePath);
DataTable dt = new DataTable();
if(!string.IsNullOrEmpty(strTableName)) {
dt.TableName = strTableName;
}
if(strExtName.Equals(".xls") || strExtName.Equals(".xlsx")) {
using(FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read)) {
HSSFWorkbook workbook = new HSSFWorkbook(file);
ISheet sheet = workbook.GetSheetAt(iSheetIndex);
//列头
foreach(ICell item in sheet.GetRow(sheet.FirstRowNum).Cells) {
dt.Columns.Add(item.ToString(), typeof(string));
}
//写入内容
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while(rows.MoveNext()) {
IRow row = (HSSFRow)rows.Current;
if(row.RowNum == sheet.FirstRowNum) {
continue;
}
DataRow dr = dt.NewRow();
foreach(ICell item in row.Cells) {
switch(item.CellType) {
case CellType.Boolean:
dr[item.ColumnIndex] = item.BooleanCellValue;
break;
case CellType.Formula:
switch(item.CachedFormulaResultType) {
case CellType.Boolean:
dr[item.ColumnIndex] = item.BooleanCellValue;
break;
case CellType.Numeric:
if(DateUtil.IsCellDateFormatted(item)) {
dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
} else {
dr[item.ColumnIndex] = item.NumericCellValue;
}
break;
case CellType.String:
string str = item.StringCellValue;
if(!string.IsNullOrEmpty(str)) {
dr[item.ColumnIndex] = str.ToString();
} else {
dr[item.ColumnIndex] = null;
}
break;
case CellType.Unknown:
case CellType.Blank:
default:
dr[item.ColumnIndex] = string.Empty;
break;
}
break;
case CellType.Numeric:
if(DateUtil.IsCellDateFormatted(item)) {
dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
} else {
dr[item.ColumnIndex] = item.NumericCellValue;
}
break;
case CellType.String:
string strValue = item.StringCellValue;
if(!string.IsNullOrEmpty(strValue)) {
dr[item.ColumnIndex] = strValue.ToString();
} else {
dr[item.ColumnIndex] = null;
}
break;
case CellType.Unknown:
case CellType.Blank:
default:
dr[item.ColumnIndex] = string.Empty;
break;
}
}
dt.Rows.Add(dr);
}
}
}
