//生成excel
private void ToolStripMenuItemExcel_Click(object sender, EventArgs e)
{
string defaultName = DateTime.Now.ToString("yyyyMMddHHmm") + "产品资料";
saveFileDialog1.FileName = defaultName + ".xls";
saveFileDialog1.Filter = "Excel文件（*.xls）|*.xls|Excel 文件(*.xlsx)|*.xlsx";
saveFileDialog1.AddExtension = true;
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
DataTable dt = ((DataSet)superGridControlMaterial.PrimaryGrid.DataSource).Tables[0];
GridColumnCollection gcc = superGridControlMaterial.PrimaryGrid.Columns;
string[] x =
{
"产品名称", "规格型号", "条码", "助记码", "编号", "所属类别", "售价", "预设售价A", "预设售价B", "预设售价C",
"预设售价D", "预设售价E", "创建时间", "供应商", "单位", "进价", "进货时间", "备注", "启用标志", "预留1", "预留2"
};
dt.Columns.Remove("Ma_ID");
dt.Columns.Remove("Ma_PicName");
dt.Columns.Remove("Ma_RFID");
dt.Columns.Remove("Ma_TypeID");
dt.Columns.Remove("Ma_SupID");
dt.Columns.Remove("Ma_Clear");
try
{
NPOIExcelHelper.DataTableToExcel(dt, "sdfe", saveFileDialog1.FileName, x);
MessageBox.Show("Excel文件已成功导出，请到保存目录下查看。");
}
catch (Exception ex)
{
MessageBox.Show("保存失败,请检查异常情况:" + ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
System.Diagnostics.Stopwatch timeWatch = System.Diagnostics.Stopwatch.StartNew();
// ExcelHelper.DatagridviewToExcel(dataGridView1, "D:/AA.xlsx");
// NPOIHelper.DataTableToExcel(dt, "测试", "D:/测试.xls");
NPOIExcelHelper.DataTableToExcel(dt, "测试", "D:/mytest1.xls");
timeWatch.Stop();
MessageBox.Show("总共耗时" + timeWatch.Elapsed);
}
//导出
private void button1_Click(object sender, EventArgs e)
{
try
{
//单个测点曲线
if (seriesCollection == null)
{
string excellCell = "F" + labelTag.TagID.ToString();
string excellName = labelTag.TagDesc;
DataTable dt = m_dtLineData.DefaultView.ToTable(false, new string[] { "ValueTime", excellCell });
dt.Columns[0].ColumnName = "时间";
dt.Columns[1].ColumnName = excellName;
bool b = NPOIExcelHelper.DataTableToExcel(dt, "", excellName);
if (b)
{
MessageBox.Show("保存成功");
}
}
//双曲线
else
{
string excellCell = "";
string[] excellName = new string[seriesCollection.Count];
int i = 0;
foreach (Series series in seriesCollection)
{
excellName[i] = series.LegendText;
excellCell += series.YValueMembers + ",";
i++;
}
excellCell = excellCell.Trim(',');
string[] strs = ("ValueTime," + excellCell).Split(',');
DataTable dt = m_dtLineData.DefaultView.ToTable(false, strs);
dt.Columns[0].ColumnName = "时间";
int count = 1;
foreach (string str in excellName)
{
dt.Columns[count].ColumnName = excellName[count - 1];
count++;
}
bool b = NPOIExcelHelper.DataTableToExcel(dt, "", "曲线数据");
if (b)
{
MessageBox.Show("保存成功");
}
}
}
catch
{ MessageBox.Show("保存失败"); }
}
//导出
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = dtChart.DefaultView.ToTable(false, "ValueTime", F18, "HourOfYear");
string title = chart1.ChartAreas[0].AxisX.Title;
dt.Columns[0].ColumnName = "时间";
dt.Columns[1].ColumnName = "累计节标煤量";
dt.Columns[2].ColumnName = "小时数";
bool b = NPOIExcelHelper.DataTableToExcel(dt, title, "累计节标煤量");
if (b)
{
MessageBox.Show("导出成功");
}
}
void TestExcelWrite(string file, DataTable dt)
{
try
{
using (NPOIExcelHelper excelHelper = new NPOIExcelHelper(file))
{
excelHelper.DataTableToExcel(dt, "Sheet1", false);
Debug.Log("Save excel file success!");
}
}
catch (Exception ex)
{
Debug.LogError("Exception: " + ex.Message);
}
}
protected override void ExportExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string defaultName = DateTime.Now.ToString("yyyyMMddHHmm") + "供应商资料";
saveFileDialog1.FileName = defaultName + ".xls";
saveFileDialog1.Filter = @"Excel 97-2003 (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
GridItemsCollection col = superGridControl1.PrimaryGrid.Rows;
GridRow row = col[0] as GridRow;
DataTable dt = new DataTable();
DataRow dr = null;
DataColumn dc = null;
for (int i = 0; i < row.Cells.Count; i++)
{
dc = new DataColumn(superGridControl1.PrimaryGrid.Columns[i].Name);
dt.Columns.Add(dc); //添加表头 当第二行的时候还会添加一次 由于table中的columns必须是唯一的 所以会导致冲突
}
for (int i = 0; i < col.Count; i++)
{
dr = dt.NewRow();
for (int j = 0; j < row.Cells.Count; j++)
{
GridRow rows = col[i] as GridRow;
dr[j] = rows.Cells[j].Value; //添加列的值
}
dt.Rows.Add(dr);
}
try
{
NPOIExcelHelper.DataTableToExcel(dt, "账户资料", saveFileDialog1.FileName);
MessageBox.Show("Excel文件已成功导出，请到保存目录下查看。");
}
catch (Exception ex)
{
MessageBox.Show("保存失败,请检查异常情况:" + ex.Message);
}
}
}
//导出
private void button1_Click(object sender, EventArgs e)
{
if (dtData != null && dtData.Rows.Count > 0)
{
try
{
DataTable dt = dtData.DefaultView.ToTable(false, new string[] { "ValueTime", excellCell });
dt.Columns[0].ColumnName = "时间";
dt.Columns[1].ColumnName = excellCellName;
bool b = NPOIExcelHelper.DataTableToExcel(dt, "", excellCellName);
if (b)
{
MessageBox.Show("保存成功");
}
}
catch
{ MessageBox.Show("保存失败"); }
}
}
private void ToolStripMenuItemExcel_Click(object sender, EventArgs e)
{
string defaultName = DateTime.Now.ToString("yyyyMMddHHmm") + "客户资料";
saveFileDialog1.FileName = defaultName + ".xls";
saveFileDialog1.Filter = "Excel文件（*.xls）|*.xls|Excel 文件(*.xlsx)|*.xlsx";
saveFileDialog1.AddExtension = true;
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
DataTable dt = (DataTable)superGridControlClient.PrimaryGrid.DataSource;
GridColumnCollection gcc = superGridControlClient.PrimaryGrid.Columns;
string[] x =
{
"客户名称", "手机", "电话", "传真", "地区", "详细地址", "联系人", "单位", "所属类别", "银行帐号",
"开户行", "新增时间", "可用额度", "剩余额度", "结账日", "应收款", "已收款", "预收款", "备注"
};
dt.Columns.Remove("Cli_ID");
dt.Columns.Remove("Cli_Code");
dt.Columns.Remove("Cli_zhiwen");
dt.Columns.Remove("Cli_PicName");
dt.Columns.Remove("Cli_CityCode");
dt.Columns.Remove("Cli_TypeCode");
dt.Columns.Remove("Cli_DiscountCode");
dt.Columns.Remove("Cli_Olddata");
dt.Columns.Remove("Cli_Oldreturn");
dt.Columns.Remove("Cli_Newoutdata");
dt.Columns.Remove("Cli_Newintodata");
dt.Columns.Remove("Cli_safetone");
dt.Columns.Remove("Cli_safettwo");
dt.Columns.Remove("Cli_Enable");
try
{
NPOIExcelHelper.DataTableToExcel(dt, "sdfe", saveFileDialog1.FileName, x);
MessageBox.Show("Excel文件已成功导出，请到保存目录下查看。");
}
catch (Exception ex)
{
MessageBox.Show("保存失败,请检查异常情况:" + ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = dtChart.DefaultView.ToTable(false, "ValueTime", F10, F6, F2, F14, F18, F19, "HourOfYear");
string title = lblMsg.Text.Substring(0, lblMsg.Text.IndexOf("双击") - 8);;
dt.Columns[0].ColumnName = "时间";
dt.Columns[1].ColumnName = "燃料累计支出（万元）";
dt.Columns[2].ColumnName = "供电累计收入（万元）";
dt.Columns[3].ColumnName = "供热累计收入（万元）";
dt.Columns[4].ColumnName = "累计毛利润（万元）";
dt.Columns[5].ColumnName = "累计节标煤量（吨）";
dt.Columns[6].ColumnName = "累计节省费用（万元）";
dt.Columns[7].ColumnName = "累计小时数";
bool b = NPOIExcelHelper.DataTableToExcel(dt, title, "收支明细");
if (b)
{
MessageBox.Show("导出成功");
}
}
DataTable TestExcelRead(string file)
{
try
{
using (NPOIExcelHelper excelHelper = new NPOIExcelHelper(file))
{
DataTable dt = excelHelper.ExcelToDataTable("Sheet1", true, 1);
Debug.Log("Load excel file success!");
EditDataTable(ref dt);
PrintData(dt);
excelHelper.DataTableToExcel(dt, "Sheet1", false);
Debug.Log("Save excel file success!");
return(dt);
}
}
catch (Exception ex)
{
Debug.LogError("Exception: " + ex.Message);
return(null);
}
}
private void button1_Click(object sender, EventArgs e)
{
string txtConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\\项目\\db\\ovehicles.mdb";
conn = new OleDbConnection(txtConn);
string txtCommand = "SELECT VEHICLE.* , '' as 排放等级 FROM VEHICLE";
OleDbDataAdapter da = new OleDbDataAdapter(txtCommand, conn);
DataSet ds = new DataSet("ds");
da.Fill(ds, "Student");
DataTable ff = ds.Tables[0];
foreach (DataRow dr in ff.Rows)
{
txtCommand = string.Format("SELECT FILENAME,PF FROM O_VEHICLES where CLXH='{0}' AND FDJXH='{1}' ORDER BY FILENAME DESC ", dr["车辆型号"], dr["发动机型号"]);
da = new OleDbDataAdapter(txtCommand, conn);
DataSet dtt = new DataSet("ds");
da.Fill(dtt, "Student");
if (dtt.Tables.Count > 0)
{
foreach (DataRow drD in dtt.Tables[0].Rows)
{
if (Convert.ToDateTime(drD["FILENAME"]) < Convert.ToDateTime(dr["出厂时间"]))
{
dr["排放等级"] = drD["PF"];
break;
}
}
}
}
NPOIExcelHelper.DataTableToExcel(ff, "测试", "D:/myt2.xls");
}
private Task <bool> ExportAsync(string name = "", Action <int> callback = null)
{
return(Task.Run(() =>
{
#region SQL 语句
var sql = @"
SET NOCOUNT ON
DECLARE @1 varchar(500),@2 VARCHAR(500),@3 VARCHAR(500) ,@4 VARCHAR(500)
DECLARE @sql VARCHAR(max), @Id VARCHAR(20)
declare system_singe cursor for
SELECT
所属人ID, 所属人, 组别,COUNT(1) cnt
FROM
dbo.t_housing_check
WHERE
所属人 NOT LIKE '%公盘%' AND 所属人ID IN (
SELECT DISTINCT userid FROM dbo.t_user_data_filter
) AND 房源编号 NOT IN (
SELECT HousingId FROM dbo.t_housing_data_filter WHERE IsFilter = 1
)
GROUP BY
所属人ID, 所属人 , 组别
ORDER BY
组别, cnt
open system_singe
fetch next from system_singe into @1,@2,@3,@4;
while @@fetch_status = 0
BEGIN
SET @sql = 'INSERT INTO dbo.t_housing_data_filter (HousingId,IsFilter)
SELECT TOP 1 房源编号 HousingId, 0 IsFilter FROM dbo.t_housing_check WHERE 房源编号 NOT IN (
SELECT HousingId FROM dbo.t_housing_data_filter WHERE IsFilter = 1
) AND 所属人ID = ' + @1
EXEC( @sql )
fetch next from system_singe into @1,@2,@3,@4;
END
close system_singe
deallocate system_singe
SELECT
房源编号 ,
楼盘名称 ,
交易类型 ,
业主姓名 ,
业主电话 ,
面积 ,
价格 ,
所属人 ,
组别 ,
区域 ,
CONVERT(VARCHAR(30),录入时间,120) 录入时间,
备注
FROM dbo.t_housing_check WHERE 房源编号 IN (
SELECT HousingId FROM dbo.t_housing_data_filter WHERE IsFilter = 0
)
";
#endregion
var ds = SQLHelper.ExecuteDataSet(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
{
var dt = ds.Tables[0];
var path = AppDomain.CurrentDomain.BaseDirectory + "Export\\广州 " + this.dtpDate.Value.ToString("yyyy-MM-dd");
if (!System.IO.Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>();
dic.Add(dt.Rows.Count + " 条记录", dt);
if (callback != null)
{
callback(dt.Rows.Count);
}
if (string.IsNullOrEmpty(name))
{
name = string.Format("广州真房源回访 ( {0}-{1} ) ", this.dtpDate.Value.ToString("MM"), this.dtpDate.Value.ToString("dd"));
}
NPOIExcelHelper.DataTableToExcel(dic, "", path + "\\" + name + ".xls");
}
return true;
}));
}
/// <summary>
///
/// </summary>
/// <param name="id">message id ,identify</param>
/// <param name="attachFolder">attacheFolder path, for web using, console app can ignore</param>
/// <param name="toSample"> will override toList to toSample, for web using now</param>
public void BuildAndSendEmail(string id = "M1", string attachFolder = null, string toSample = null)
{
try
{
//Console.WriteLine("start send M1 Mail...");
//string id = "M1";
EW_MESSAGE_BLL msgBLL = new EW_MESSAGE_BLL();
EW_MESSAGE msg = msgBLL.GetMsgByID(id);
//Emailhelper
EmailHelper ehelper = new EmailHelper();
List <string> toList = new List <string>();// { "*****@*****.**" };//[email protected]
List <string> ccList = new List <string>()
{
"*****@*****.**"
};
if (string.IsNullOrEmpty(toSample))
{
string[] toArray = msg.TO_LIST.Split(';');
string[] ccArray = msg.CC_LIST.Split(';');
toList.AddRange(toArray);
ccList.AddRange(ccArray);
//Get apply to list
EW_USER_APPLY_BLL aBLL = new EW_USER_APPLY_BLL();
List <string> applyUserMails = aBLL.GetApplyUsersMail(id);
if (applyUserMails != null)
{
foreach (var item in applyUserMails)
{
//Just have appuser name , email is null situation
if (item.IndexOf("@") < 0)
{
toList.Add(string.Format(CHubConstValues.EmailFormat, item));
}
else
{
toList.Add(item);
}
}
}
toList.Distinct();
}
else
{
toList.Clear();
toList.Add(toSample);
ccList.Clear();
}
string from = CHubConstValues.MailFromAddr;
StringBuilder body = BuildEmailBody(msg);
EW_MESSAGE_ATTACH_BLL attachBLL = new EW_MESSAGE_ATTACH_BLL();
List <EW_MESSAGE_ATTACH> attaches = attachBLL.GetAttachByMsgID(msg.MESSAGE_ID);
EW_SCRIPT_BLL scriptBLL = new EW_SCRIPT_BLL();
CHubEntities db = new CHubEntities();
List <string> attachPathList = new List <string>();
foreach (var item in attaches)
{
EW_SCRIPT script = scriptBLL.GetScriptByID(item.SCRIPT_ID);
DataTable dt = db.Database.SqlQueryToDataTatable(script.SCRIPT_TEXT);
//if NO_DATA_IGNORE is Y , will ignore empty dt
if (item.NO_DATA_IGNORE == CHubConstValues.IndY && (dt == null || dt.Rows.Count == 0))
{
continue;
}
if (item.IN_CONTENT == CHubConstValues.IndY)
{
body.AppendLine(ehelper.BuildBodyFromDT(dt, script.EXPORT_FNAME.Substring(0, script.EXPORT_FNAME.Length - 3)));
}
else
{
if (attachFolder == null)
{
attachFolder = CHubConstValues.EmailAttachFolder;
}
FileInfo folder = new FileInfo(attachFolder);
if (!Directory.Exists(folder.FullName))
{
Directory.CreateDirectory(folder.FullName);
}
string attachPath = folder.FullName + string.Format(script.EXPORT_FNAME, DateTime.Now.ToString("yyyy_MM_dd_hh_mm")) + ".xlsx";
NPOIExcelHelper excelHelper = new NPOIExcelHelper(attachPath);
excelHelper.DataTableToExcel(dt, "M1 sheet");
attachPathList.Add(attachPath);
}
//break;
}
ehelper.SendEmail(toList.ToArray(), ccList.ToArray(), from, msg.MESSAGE_SUBJECT, body.ToString(), attachPathList);
}
catch (Exception ex)
{
string msg = ex.Message;
Console.WriteLine("exception:" + ex.Message);
}
}
private Task <bool> ExportAsync(string name = "", Action <int> callback = null)
{
return(Task.Run(() =>
{
#region SQL 语句
var sql = @"
SET NOCOUNT ON
DECLARE @1 varchar(500),@2 VARCHAR(500)
DECLARE @sql VARCHAR(max), @Id VARCHAR(20)
declare system_singe cursor for
SELECT
所属店组 ,
COUNT(1) cnt
FROM
dbo.t_housing_data
GROUP BY
所属店组
ORDER BY
所属店组, cnt
open system_singe
fetch next from system_singe into @1,@2;
while @@fetch_status = 0
BEGIN
SET @sql = 'INSERT INTO dbo.t_housing_data_filter (Id,IsFilter)
SELECT TOP 1 房源编号 Id, 0 IsFilter FROM dbo.t_housing_data WHERE 房源编号 NOT IN (
SELECT Id FROM dbo.t_housing_data_filter WHERE IsFilter <> 0
) AND 所属店组 = ''' + @1 + ''''
EXEC( @sql )
fetch next from system_singe into @1,@2;
END
close system_singe
deallocate system_singe
SELECT
房源编号 ,
业主姓名 ,
房源业主电话 ,
录入人 ,
录入大区 ,
录入区 ,
录入店组 ,
所属人 ,
所属大区 ,
所属区 ,
所属店组 ,
委托来源大类 ,
委托来源细分 ,
类型 ,
带看次数 ,
最近一次带看时间 ,
是否实勘 ,
是否有钥匙 ,
居室 ,
城区 ,
楼盘名称 ,
新建时间 ,
挂牌出售价格 ,
挂牌月租金 ,
建筑面积 ,
出租面积
FROM dbo.t_housing_data WHERE 房源编号 IN (
SELECT Id FROM dbo.t_housing_data_filter WHERE IsFilter = 0
)";
#endregion
var ds = SQLHelper.ExecuteDataSet(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
{
var dt = ds.Tables[0];
var path = AppDomain.CurrentDomain.BaseDirectory + "Export\\深圳链家 " + this.dtpDate.Value.ToString("yyyy-MM-dd");
if (!System.IO.Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>();
dic.Add(dt.Rows.Count + " 条记录", dt);
if (callback != null)
{
callback(dt.Rows.Count);
}
if (string.IsNullOrEmpty(name))
{
name = string.Format("深圳链家真房源回访 ( {0}-{1} ) ", this.dtpDate.Value.ToString("MM"), this.dtpDate.Value.ToString("dd"));
}
NPOIExcelHelper.DataTableToExcel(dic, "", path + "\\" + name + ".xls");
}
return true;
}));
}