private void btnExport_Click(object sender, EventArgs e)
{
#region collect the format of columns
Collection c = new Collection();
foreach (DataGridViewRow item in this.dataFormatTable.Rows)
{
if (Boolean.Parse(item.Cells[0].Value.ToString()))
{
c.Add(item);
}
}
#endregion
#region get query sql string
StringBuilder sb = new StringBuilder("select ");
if (c.Count < 1)
{
MessageBox.Show("至少要选择一列");
return;
}
foreach (DataGridViewRow item in c)
{
sb.Append(item.Cells[4].Value + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" from (" + config.Script.Sql + ") as temp");
string sql = sb.ToString();
/*MessageBox.Show(sql);
* return;*/
#endregion
#region make sure that the user have checkd all and check the connection
int result = (int)Interaction.MsgBox("确定要导出吗?", MsgBoxStyle.OkCancel, "提示");
if (result != 1)
{
return;
}
if (con.State != ConnectionState.Open)
{
try
{
con.Open();
}
catch (Exception ex)
{
MessageBox.Show("数据库连接建立失败!\r\n" + ex.Message,
"提示",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
}
#endregion
#region execute the sql
DataTable rdt;
try
{
rdt = DBUtils.execSql(con, sql);
}
catch (Exception ex)
{
MessageBox.Show("数据查询失败!\r\n" + ex.Message,
"提示",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
#endregion
#region parse data from server
var columns = new List <Common.Excel.Column>();
foreach (DataGridViewRow item in c)
{
columns.Add(new Common.Excel.Column(item.Cells[4].Value.ToString(),
item.Cells[1].Value.ToString(),
(ColumnDataType)((ComboName)(Enum.Parse(typeof(ComboName), item.Cells[2].Value.ToString()))),
int.Parse(item.Cells[3].Value.ToString())));
}
#endregion
#region [Old Version]export to excel file
/*var fileDialog = new SaveFileDialog();
* fileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
* fileDialog.Filter = "Excel文档(*.xls)|*xls";
* fileDialog.RestoreDirectory = true;
* fileDialog.FileName = config.Name+DateTime.Now.ToString("HH-mm-ss", DateTimeFormatInfo.InvariantInfo)+".xls";
* var rel = fileDialog.ShowDialog();
* if (rel == DialogResult.OK)
* {
* string localFilePath = fileDialog.FileName;
* using (var workbook = new Common.Excel.Workbook())
* {
* var sheet = new Common.Excel.Sheet("数据", columns, rdt);
* workbook.CreateSheet(sheet);
*
* using (var ms = workbook.GetMemoryStream())
* {
* var fs = new System.IO.FileStream(localFilePath, System.IO.FileMode.Create);
* byte[] buff = ms.ToArray();
* fs.Write(buff, 0, buff.Length);
* fs.Flush();
* fs.Close();
* }
* }
*
* MessageBox.Show("保存成功。");
*
* }*/
#endregion
#region [New Version]export to excel file
//check if the directory exist
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
string FileName = config.Script.Name + DateTime.Now.ToString("HH-mm-ss", DateTimeFormatInfo.InvariantInfo) + ".xlsx";
try
{
using (var workbook = new Common.Excel.Workbook())
{
var sheet = new Common.Excel.Sheet("数据", columns, rdt);
workbook.CreateSheet(sheet);
using (var ms = workbook.GetMemoryStream())
{
var fs = new System.IO.FileStream(savePath + "\\" + FileName, System.IO.FileMode.Create);
byte[] buff = ms.ToArray();
fs.Write(buff, 0, buff.Length);
fs.Flush();
fs.Close();
}
}
MessageBox.Show("导出文件成功\r\n" + "文件路径:" + savePath + "\\" + FileName,
"提示",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}catch (Exception ex)
{
MessageBox.Show("文件导出失败\r\n" + ex.Message,
"提示",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
#endregion
}
private void loadColumns()
{
/*if (totalCount == 0)
* {
* MessageBox.Show("请先设置脚本!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
*
* return;
* }*/
#region try to set connettion
try
{
if (con != null)
{
con.Close();
}
con = new SqlConnection(DBUtils.GetConnetionString(config.DataSource.Ip, config.DataSource.User, config.DataSource.Pwd, config.Script.Db));
con.Open();
}
catch (Exception ex)
{
MessageBox.Show("该脚本已不可用,请重新自定义或联系逐浪官方\r\n" + ex.Message,
"提示",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
#endregion
#region check connection and query columns
if (con.State != ConnectionState.Open)
{
MessageBox.Show("连接未打开!\r\n请重新测试脚本 或 重新加载", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//从数据库获取列名
string sql = "select top 0 * from (" + config.Script.Sql + ") as temp";
DataTable dt;
try
{
dt = DBUtils.execSql(con, sql);
}
catch (Exception ex)
{
MessageBox.Show("您的脚本语句有语法错误,具体如下\r\n" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#endregion
#region try to parse the data from server then bind to DataGridView
this.dataFormatTable.Rows.Clear();
for (int i = 0; i < dt.Columns.Count; i++)
{
DataGridViewRow r = new DataGridViewRow();
DataGridViewCheckBoxCell stateCheckBox = new DataGridViewCheckBoxCell();
stateCheckBox.Value = true;
DataGridViewComboBoxCell typeComboList = new DataGridViewComboBoxCell();
typeComboList.Items.Add("普通文本");
typeComboList.Items.Add("日期");
typeComboList.Items.Add("日期和时间");
typeComboList.Items.Add("真假");
typeComboList.Items.Add("整型");
typeComboList.Items.Add("浮点型");
typeComboList.Items.Add("超链接");
typeComboList.Items.Add("其他");
typeComboList.Items.Add("小数型");
DBType dBType = (DBType)Enum.Parse(typeof(DBType), (dt.Columns[i].DataType).ToString().Split('.')[1]);
typeComboList.Value = ((ComboName)((int)dBType)).ToString();
DataGridViewTextBoxCell nameBox = new DataGridViewTextBoxCell();
nameBox.Value = dt.Columns[i].ColumnName;
DataGridViewTextBoxCell widthBox = new DataGridViewTextBoxCell();
widthBox.Value = 10;
DataGridViewTextBoxCell hideColumnName = new DataGridViewTextBoxCell();
hideColumnName.Value = dt.Columns[i].ColumnName;
r.Cells.Add(stateCheckBox);
r.Cells.Add(nameBox);
r.Cells.Add(typeComboList);
r.Cells.Add(widthBox);
r.Cells.Add(hideColumnName);
this.dataFormatTable.Rows.Add(r);
}
#endregion
#region step control
this.tabPage2.Parent = this.stepControl;
this.tabPage1.Parent = null;
this.tabPage3.Parent = null;
#endregion
}
