using DevExpress.XtraEditors;
|
using NPOI.HSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.IO;
|
using System.Windows.Forms;
|
|
namespace IStation.WinFrmUI.Curve
|
{
|
public partial class ExportToXLS
|
{
|
//static string InvalidFileNameChar = "/:*?\"<>|";
|
|
//导出单条曲线
|
public static void Export(List<Model.CurvePoint> points, string strDefaultFileName = "曲线")
|
{
|
try
|
{
|
//选择文件
|
SaveFileDialog saveFileDlg = new SaveFileDialog();
|
//saveFileDlg.FileName = strDefaultFileName;
|
saveFileDlg.Filter = "EXCEL 文件(*.xls)|*.xls";
|
if (saveFileDlg.ShowDialog() != DialogResult.OK)
|
return;
|
string strXlsFile = saveFileDlg.FileName;
|
string fileName = Path.GetFileName(strXlsFile);
|
|
int i = 0;
|
for (i = 0; i < InvalidFileNameChar.Length; i++)
|
{
|
if (fileName.Contains(InvalidFileNameChar[i].ToString()))
|
{
|
throw new Exception(string.Format("{0}含有特殊符号{1},不能作为文件名,请修改",
|
strXlsFile, InvalidFileNameChar[i]));
|
}
|
}
|
|
#region 初始化表格
|
HSSFWorkbook theBook = InitializeBook(strXlsFile);
|
if (theBook == null)
|
{
|
return;
|
}
|
|
var theSheet1 = theBook.GetSheet("Sheet1");
|
if (theSheet1 == null)
|
return;
|
#endregion
|
|
NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
|
|
rowTile.CreateCell(0).SetCellValue("序号");//
|
rowTile.CreateCell(1).SetCellValue("流量");
|
rowTile.CreateCell(2).SetCellValue("扬程");
|
|
|
for (i = 0; i < points.Count; i++)
|
{
|
var pt = points[i];
|
int col = 0;
|
NPOI.SS.UserModel.IRow rowtemp = theSheet1.CreateRow(i + 1);
|
|
rowtemp.CreateCell(col).SetCellValue(i + 1);//序号
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(MyTrans(pt.X));
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(MyTrans(pt.Y));
|
col++;
|
}
|
|
//Force excel to recalculate all the formula while open
|
theSheet1.ForceFormulaRecalculation = true;
|
|
//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件!
|
using (FileStream fs = File.OpenWrite(strXlsFile))
|
{
|
theBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
|
|
// XtraMessageBox.Show("导出成功!");
|
}
|
|
//WinFrmUI.FileMessageBox.Show(strXlsFile, SPump.WinFrmUI.Localization.Current);
|
|
}
|
catch (TimeoutException ex)
|
{
|
XtraMessageBox.Show("通讯超时，请重试", "Error:249 " + ex.Message);
|
return;
|
}
|
catch (Exception ex)
|
{
|
XtraMessageBox.Show(ex.ToString());
|
}
|
}
|
|
/*public static void Export(ViewModel.LxpFeatChartCurveGroup LxpCurveGroup, int FitPointNumQH = 15)
|
{
|
Export(LxpCurveGroup.FeatCurveQH.CurveExpress, LxpCurveGroup.FeatCurveQE.CurveExpress,
|
LxpCurveGroup.FeatCurveQP.CurveExpress,
|
FitPointNumQH);
|
}*/
|
|
static string InvalidFileNameChar = "/:*?\"<>|";
|
public static void Export(
|
Model.CurveExpress CurveExpressQH,
|
Model.CurveExpress CurveExpressQE,
|
Model.CurveExpress CurveExpressQP,
|
int FitPointNumQH = 15)
|
{
|
try
|
{
|
//选择文件
|
SaveFileDialog saveFileDlg = new SaveFileDialog();
|
//saveFileDlg.FileName = strDefaultFileName;
|
saveFileDlg.Filter = "EXCEL 文件(*.xls)|*.xls";
|
if (saveFileDlg.ShowDialog() != DialogResult.OK)
|
return;
|
string strXlsFile = saveFileDlg.FileName;
|
string fileName = Path.GetFileName(strXlsFile);
|
|
int i = 0;
|
for (i = 0; i < InvalidFileNameChar.Length; i++)
|
{
|
if (fileName.Contains(InvalidFileNameChar[i].ToString()))
|
{
|
throw new Exception(string.Format("{0}含有特殊符号{1},不能作为文件名,请修改",
|
strXlsFile, InvalidFileNameChar[i]));
|
}
|
}
|
|
#region 初始化表格
|
HSSFWorkbook theBook = InitializeBook(strXlsFile);
|
if (theBook == null)
|
{
|
return;
|
}
|
|
var theSheet1 = theBook.GetSheet("Sheet1");
|
if (theSheet1 == null)
|
return;
|
#endregion
|
NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
|
rowTile.CreateCell(0).SetCellValue("序号");//
|
rowTile.CreateCell(1).SetCellValue("流量");
|
rowTile.CreateCell(2).SetCellValue("扬程");
|
rowTile.CreateCell(3).SetCellValue("效率");
|
rowTile.CreateCell(4).SetCellValue("功率");
|
|
//均匀插值点,计算点坐标
|
double rQmin = CurveExpressQH.Min;
|
double rQmax = CurveExpressQH.Max;
|
double space = (rQmax - rQmin) / (FitPointNumQH - 1);
|
double Q = rQmin;
|
double H, E, P;
|
for (i = 0; i < FitPointNumQH; i++)
|
{
|
H = Model.FitCurveHelper.GetFitPointY(CurveExpressQH, Q);
|
P = Model.FitCurveHelper.GetFitPointY(CurveExpressQP, Q);
|
var eta = Model.FitCurveHelper.GetFitPointY(CurveExpressQE, Q);
|
|
|
int col = 0;
|
NPOI.SS.UserModel.IRow rowtemp = theSheet1.CreateRow(i + 1);
|
|
rowtemp.CreateCell(col).SetCellValue(i + 1);//序号
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(MyTrans(Q));
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(MyTrans(H));
|
col++;
|
|
if (CurveExpressQE != null
|
&& Q <= CurveExpressQE.Max
|
&& Q >= CurveExpressQE.Min)
|
{
|
if (Q < 0.1)
|
E = 0;
|
else
|
E = Model.FitCurveHelper.GetFitPointY(CurveExpressQE, Q);
|
rowtemp.CreateCell(col).SetCellValue(MyTrans(E));
|
col++;
|
}
|
|
|
if (CurveExpressQP != null
|
&& Q <= CurveExpressQP.Max
|
&& Q >= CurveExpressQP.Min)
|
{
|
P = Model.FitCurveHelper.GetFitPointY(CurveExpressQP, Q);
|
rowtemp.CreateCell(col).SetCellValue(MyTrans(P));
|
col++;
|
}
|
|
|
Q = Q + space;
|
if (Q > CurveExpressQH.Max * 0.9999)
|
Q = CurveExpressQH.Max * 0.9999;
|
}
|
|
//Force excel to recalculate all the formula while open
|
theSheet1.ForceFormulaRecalculation = true;
|
|
//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件!
|
using (FileStream fs = File.OpenWrite(strXlsFile))
|
{
|
theBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
|
|
// XtraMessageBox.Show("导出成功!");
|
}
|
|
//WinFrmUI.FileMessageBox.Show(strXlsFile, SPump.WinFrmUI.Localization.Current);
|
|
}
|
catch (TimeoutException ex)
|
{
|
XtraMessageBox.Show("通讯超时，请重试", "Error:249 " + ex.Message);
|
return;
|
}
|
catch (Exception ex)
|
{
|
XtraMessageBox.Show(ex.ToString());
|
}
|
|
}
|
|
//初始化表格:复制Blank.xls
|
private static HSSFWorkbook InitializeBook(string strFullName, string TemplateFileName = "Blank")
|
{
|
string strTemplateFile = Application.StartupPath + "\\Template\\ImportPumpFeatCurveStd1.xls";
|
if (!File.Exists(strTemplateFile))
|
{
|
return null;
|
}
|
File.Copy(strTemplateFile, strFullName);
|
|
HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(strFullName, FileMode.Open, FileAccess.Read))
|
{
|
theBook = new HSSFWorkbook(file);
|
}
|
|
//create application entry of DocumentSummaryInformation
|
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
|
dsi.Company = "EvenTech";
|
theBook.DocumentSummaryInformation = dsi;
|
|
//create application entry of SummaryInformation
|
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
|
si.Subject = "yiwei";
|
theBook.SummaryInformation = si;
|
|
return theBook;
|
}
|
|
private static string MyTrans(double doubleValue)
|
{
|
if (doubleValue < 1)
|
return doubleValue.ToString("0.0000");
|
else if (doubleValue < 10)
|
return doubleValue.ToString("0.000");
|
else if (doubleValue < 100)
|
return doubleValue.ToString("0.00");
|
else
|
return doubleValue.ToString("0.0");
|
//return ((int)(doubleValue * 10000) / 10000.0).ToString();
|
}
|
|
|
public static void Export_Custom(
|
List<Model.CurvePoint> Points)
|
{
|
try
|
{
|
//选择文件
|
SaveFileDialog saveFileDlg = new SaveFileDialog();
|
//saveFileDlg.FileName = strDefaultFileName;
|
saveFileDlg.Filter = "EXCEL 文件(*.xls)|*.xls";
|
if (saveFileDlg.ShowDialog() != DialogResult.OK)
|
return;
|
string strXlsFile = saveFileDlg.FileName;
|
string fileName = Path.GetFileName(strXlsFile);
|
|
int i = 0;
|
for (i = 0; i < InvalidFileNameChar.Length; i++)
|
{
|
if (fileName.Contains(InvalidFileNameChar[i].ToString()))
|
{
|
throw new Exception(string.Format("{0}含有特殊符号{1},不能作为文件名,请修改",
|
strXlsFile, InvalidFileNameChar[i]));
|
}
|
}
|
|
if (File.Exists(strXlsFile))
|
{
|
File.Delete(strXlsFile);
|
}
|
#region 初始化表格
|
HSSFWorkbook theBook = InitializeBook(strXlsFile);
|
if (theBook == null)
|
{
|
return;
|
}
|
|
var theSheet1 = theBook.GetSheet("Sheet1");
|
if (theSheet1 == null)
|
return;
|
#endregion
|
|
//标题
|
NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
|
rowTile.CreateCell(0).SetCellValue("序号");
|
rowTile.CreateCell(1).SetCellValue("流量");
|
rowTile.CreateCell(2).SetCellValue("扬程");
|
rowTile.CreateCell(3).SetCellValue("效率");
|
rowTile.CreateCell(4).SetCellValue("功率");
|
|
//均匀插值点,计算点坐标
|
|
for (i = 0; i < Points.Count; i++)
|
{
|
|
var pt = Points[i];
|
|
int col = 0;
|
NPOI.SS.UserModel.IRow rowtemp = theSheet1.CreateRow(i + 1);
|
|
rowtemp.CreateCell(col).SetCellValue(i + 1);//序号
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(pt.X);
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(pt.Y);
|
col++;
|
|
}
|
|
//Force excel to recalculate all the formula while open
|
theSheet1.ForceFormulaRecalculation = true;
|
|
//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件!
|
using (FileStream fs = File.OpenWrite(strXlsFile))
|
{
|
theBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
|
XtraMessageBox.Show("导出成功!");
|
}
|
|
//WinFrmUI.FileMessageBox.Show(strXlsFile, SPump.WinFrmUI.Localization.Current);
|
|
}
|
catch (TimeoutException ex)
|
{
|
XtraMessageBox.Show("通讯超时，请重试", "Error:249 " + ex.Message);
|
return;
|
}
|
catch (Exception ex)
|
{
|
XtraMessageBox.Show(ex.ToString());
|
}
|
}
|
|
//初始化表格:复制Blank.xls
|
private static HSSFWorkbook InitializeBook1(string strFullName, string TemplateFileName = "Blank")
|
{
|
|
HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(strFullName, FileMode.Open, FileAccess.Read))
|
{
|
theBook = new HSSFWorkbook(file);
|
}
|
|
//create application entry of DocumentSummaryInformation
|
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
|
dsi.Company = "EvenTech";
|
theBook.DocumentSummaryInformation = dsi;
|
|
//create application entry of SummaryInformation
|
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
|
si.Subject = "yiwei";
|
theBook.SummaryInformation = si;
|
|
return theBook;
|
}
|
}
|
}
|
