参数线性拟合类：包含（一次线性、二次线性、幂函数、E的指数、对数）

首先是参数拟合方法类：

  1 using System;
  2 using System.Collections.Generic;
  3 
  4 namespace Common
  5 {
  6     /// <summary>
  7     /// 参数拟合
  8     /// </summary>
  9     public class FittingFunct
 10     {
 11         #region 多项式拟合函数,输出系数是y=a0+a1*x+a2*x*x+.........，按a0,a1,a2输出
 12         static public double[] Polyfit(double[] y, double[] x, int order)
 13         {
 14             double[,] guass = Get_Array(y, x, order);
 15 
 16             double[] ratio = Cal_Guass(guass, order + 1);
 17 
 18             return ratio;
 19         }
 20         #endregion
 21 
 22         #region 一次拟合函数，y=a0+a1*x,输出次序是a0,a1
 23         static public double[] Linear(double[] y, double[] x)
 24         {
 25             double[] ratio = Polyfit(y, x, 1);
 26             return ratio;
 27         }
 28         #endregion
 29 
 30         #region 一次拟合函数，截距为0，y=a0x,输出次序是a0
 31         static public double[] LinearInterceptZero(double[] y, double[] x)
 32         {
 33             double divisor = 0; //除数
 34             double dividend = 0; //被除数
 35             for (int i = 0; i < x.Length; i++)
 36             {
 37                 divisor += x[i] * x[i];
 38                 dividend += x[i] * y[i];
 39             }
 40             if (divisor == 0)
 41             {
 42                 throw (new Exception("除数不为0！"));
 43             }
 44             return new double[] { dividend / divisor };
 45 
 46         }
 47         #endregion
 48 
 49         #region 二次拟合函数，y=a0+a1*x+a2x²,输出次序是a0,a1,a2
 50         static public double[] TowTimesCurve(double[] y, double[] x)
 51         {
 52             double[] ratio = Polyfit(y, x, 2);
 53             return ratio;
 54         }
 55         #endregion
 56 
 57         #region 对数拟合函数,.y= c*(ln x)+b,输出为b,c
 58         static public double[] LOGEST(double[] y, double[] x)
 59         {
 60             double[] lnX = new double[x.Length];
 61 
 62             for (int i = 0; i < x.Length; i++)
 63             {
 64                 if (x[i] == 0 || x[i] < 0)
 65                 {
 66                     throw (new Exception("正对非正数取对数！"));
 67                 }
 68                 lnX[i] = Math.Log(x[i]);
 69             }
 70 
 71             return Linear(y, lnX);
 72         }
 73         #endregion
 74 
 75         #region 幂函数拟合模型, y=c*x^b,输出为c,b
 76         static public double[] PowEST(double[] y, double[] x)
 77         {
 78             double[] lnX = new double[x.Length];
 79             double[] lnY = new double[y.Length];
 80             double[] dlinestRet;
 81 
 82             for (int i = 0; i < x.Length; i++)
 83             {
 84                 lnX[i] = Math.Log(x[i]);
 85                 lnY[i] = Math.Log(y[i]);
 86             }
 87 
 88             dlinestRet = Linear(lnY, lnX);
 89 
 90             dlinestRet[0] = Math.Exp(dlinestRet[0]);
 91 
 92             return dlinestRet;
 93         }
 94         #endregion
 95 
 96         #region 指数函数拟合函数模型，公式为 y=c*m^x;输出为 c,m
 97         static public double[] IndexEST(double[] y, double[] x)
 98         {
 99             double[] lnY = new double[y.Length];
100             double[] ratio;
101             for (int i = 0; i < y.Length; i++)
102             {
103                 lnY[i] = Math.Log(y[i]);
104             }
105 
106             ratio = Linear(lnY, x);
107             for (int i = 0; i < ratio.Length; i++)
108             {
109                 if (i == 0)
110                 {
111                     ratio[i] = Math.Exp(ratio[i]);
112                 }
113             }
114             return ratio;
115         }
116         #endregion
117 
118         #region 相关系数R²部分
119         public static double Pearson(IEnumerable<double> dataA, IEnumerable<double> dataB)
120         {
121             int n = 0;
122             double r = 0.0;
123 
124             double meanA = 0;
125             double meanB = 0;
126             double varA = 0;
127             double varB = 0;
128             int ii = 0;
129             using (IEnumerator<double> ieA = dataA.GetEnumerator())
130             using (IEnumerator<double> ieB = dataB.GetEnumerator())
131             {
132                 while (ieA.MoveNext())
133                 {
134                     if (!ieB.MoveNext())
135                     {
136                         //throw new ArgumentOutOfRangeException("dataB", Resources.ArgumentArraysSameLength);
137                     }
138                     ii++;
139                     //Console.WriteLine("FF00::  " + ii + " --  " + meanA + " -- " + meanB + " -- " + varA + "  ---  " + varB);
140                     double currentA = ieA.Current;
141                     double currentB = ieB.Current;
142 
143                     double deltaA = currentA - meanA;
144                     double scaleDeltaA = deltaA / ++n;
145 
146                     double deltaB = currentB - meanB;
147                     double scaleDeltaB = deltaB / n;
148 
149                     meanA += scaleDeltaA;
150                     meanB += scaleDeltaB;
151 
152                     varA += scaleDeltaA * deltaA * (n - 1);
153                     varB += scaleDeltaB * deltaB * (n - 1);
154                     r += (deltaA * deltaB * (n - 1)) / n;
155                     //Console.WriteLine("FF00::  " + ii + " --  " + meanA + " -- " + meanB + " -- " + varA + "  ---  " + varB);
156                 }
157 
158                 if (ieB.MoveNext())
159                 {
160                     //throw new ArgumentOutOfRangeException("dataA", Resources.ArgumentArraysSameLength);
161                 }
162             }
163             return (r / Math.Sqrt(varA * varB)) * (r / Math.Sqrt(varA * varB));
164         }
165         #endregion
166 
167         #region 最小二乘法部分
168 
169         #region 计算增广矩阵
170         static private double[] Cal_Guass(double[,] guass, int count)
171         {
172             double temp;
173             double[] x_value;
174 
175             for (int j = 0; j < count; j++)
176             {
177                 int k = j;
178                 double min = guass[j, j];
179 
180                 for (int i = j; i < count; i++)
181                 {
182                     if (Math.Abs(guass[i, j]) < min)
183                     {
184                         min = guass[i, j];
185                         k = i;
186                     }
187                 }
188 
189                 if (k != j)
190                 {
191                     for (int x = j; x <= count; x++)
192                     {
193                         temp = guass[k, x];
194                         guass[k, x] = guass[j, x];
195                         guass[j, x] = temp;
196                     }
197                 }
198 
199                 for (int m = j + 1; m < count; m++)
200                 {
201                     double div = guass[m, j] / guass[j, j];
202                     for (int n = j; n <= count; n++)
203                     {
204                         guass[m, n] = guass[m, n] - guass[j, n] * div;
205                     }
206                 }
207 
208                 /* System.Console.WriteLine("初等行变换：");
209                  for (int i = 0; i < count; i++)
210                  {
211                      for (int m = 0; m < count + 1; m++)
212                      {
213                          System.Console.Write("{0,10:F6}", guass[i, m]);
214                      }
215                      Console.WriteLine();
216                  }*/
217             }
218             x_value = Get_Value(guass, count);
219 
220             return x_value;
221 
222             /*if (x_value == null)
223                 Console.WriteLine("方程组无解或多解！");
224             else
225             {
226                 foreach (double x in x_value)
227                 {
228                     Console.WriteLine("{0:F6}", x);
229                 }
230             }*/
231         }
232 
233         #endregion
234 
235         #region 回带计算X值
236         static private double[] Get_Value(double[,] guass, int count)
237         {
238             double[] x = new double[count];
239             double[,] X_Array = new double[count, count];
240             int rank = guass.Rank;//秩是从0开始的
241 
242             for (int i = 0; i < count; i++)
243                 for (int j = 0; j < count; j++)
244                     X_Array[i, j] = guass[i, j];
245 
246             if (X_Array.Rank < guass.Rank)//表示无解
247             {
248                 return null;
249             }
250 
251             if (X_Array.Rank < count - 1)//表示有多解
252             {
253                 return null;
254             }
255             //回带计算x值
256             x[count - 1] = guass[count - 1, count] / guass[count - 1, count - 1];
257             for (int i = count - 2; i >= 0; i--)
258             {
259                 double temp = 0;
260                 for (int j = i; j < count; j++)
261                 {
262                     temp += x[j] * guass[i, j];
263                 }
264                 x[i] = (guass[i, count] - temp) / guass[i, i];
265             }
266 
267             return x;
268         }
269         #endregion
270 
271         #region  得到数据的法矩阵,输出为发矩阵的增广矩阵
272         static private double[,] Get_Array(double[] y, double[] x, int n)
273         {
274             double[,] result = new double[n + 1, n + 2];
275 
276             if (y.Length != x.Length)
277             {
278                 throw (new Exception("两个输入数组长度不一！"));
279                 //return null;
280             }
281 
282             for (int i = 0; i <= n; i++)
283             {
284                 for (int j = 0; j <= n; j++)
285                 {
286                     result[i, j] = Cal_sum(x, i + j);
287                 }
288                 result[i, n + 1] = Cal_multi(y, x, i);
289             }
290 
291             return result;
292         }
293 
294         #endregion
295 
296         #region 累加的计算
297         static private double Cal_sum(double[] input, int order)
298         {
299             double result = 0;
300             int length = input.Length;
301 
302             for (int i = 0; i < length; i++)
303             {
304                 result += Math.Pow(input[i], order);
305             }
306 
307             return result;
308         }
309         #endregion
310 
311         #region 计算∑(x^j)*y
312         static private double Cal_multi(double[] y, double[] x, int order)
313         {
314             double result = 0;
315 
316             int length = x.Length;
317 
318             for (int i = 0; i < length; i++)
319             {
320                 result += Math.Pow(x[i], order) * y[i];
321             }
322 
323             return result;
324         }
325         #endregion
326 
327         #endregion
328     }
329 }

View Code--FittingFunct

搭建界面如图

导入参数为表格的两列数据，表格格式为.xls，内容如图

然后勾选拟合参数模型，点击计算

界面的xaml代码：

  1 <window x:Class="Common.Control.View.LinearFuncWin"
  2              xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  3              xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  4              xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
  5              xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
  6              xmlns:c="clr-namespace:Common;assembly=Common"
  7              xmlns:i="clr-namespace:System.Windows.Interactivity;assembly=System.Windows.Interactivity"
  8              xmlns:local="clr-namespace:Common.Control.View"
  9              mc:Ignorable="d" 
 10            Height="355" Width="500" WindowStyle="ToolWindow" ResizeMode="NoResize"
 11         WindowStartupLocation="CenterScreen"  Topmost="True"  Title="参数拟合" >
 12 
 13     <Window.Resources>
 14         <ResourceDictionary>
 15             <Style x:Key="dgCell" TargetType="TextBlock">
 16                 <Setter Property="TextAlignment" Value="Center"/>
 17             </Style>
 18         </ResourceDictionary>
 19     </Window.Resources>
 20     <Grid>
 21         <Grid.RowDefinitions>
 22             <RowDefinition Height="10"/>
 23             <RowDefinition Height="30"/>
 24             <RowDefinition Height="25"/>
 25             <RowDefinition Height="25"/>
 26             <RowDefinition Height="25"/>
 27             <RowDefinition Height="25"/>
 28             <RowDefinition Height="25"/>
 29             <RowDefinition Height="25"/>
 30             <RowDefinition Height="25"/>
 31             <RowDefinition Height="25"/>
 32 
 33             <RowDefinition Height="25"/>
 34             <RowDefinition Height="50"/>
 35         </Grid.RowDefinitions>
 36         <Grid.ColumnDefinitions>
 37             <ColumnDefinition Width="20"/>
 38             <ColumnDefinition Width="3*"/>
 39             <ColumnDefinition Width="3*"/>
 40             <ColumnDefinition Width="3*"/>
 41             <ColumnDefinition Width="3*"/>
 42             <ColumnDefinition Width="20"/>
 43         </Grid.ColumnDefinitions>
 44         <Grid.Resources>
 45             <Style x:Key="{x:Type Button}"  TargetType="{x:Type Button}" >
 46                 <Setter Property="Foreground" Value="#7BA0DF"></Setter>
 47                 <Setter Property="Background" Value="WhiteSmoke"></Setter>
 48                 <Setter Property="Template">
 49                     <Setter.Value>
 50                         <ControlTemplate TargetType="{x:Type Button}">
 51                             <Border x:Name="border" BorderBrush="#7BA0DF" BorderThickness="{TemplateBinding BorderThickness}" Background="{TemplateBinding Background}" 
 52                                 SnapsToDevicePixels="true" CornerRadius="3,3,3,3">
 53                                 <ContentPresenter x:Name="contentPresenter" 
 54                                               Focusable="False" 
 55                                               HorizontalAlignment="{TemplateBinding HorizontalContentAlignment}" 
 56                                               Margin="{TemplateBinding Padding}" 
 57                                               RecognizesAccessKey="True" 
 58                                               SnapsToDevicePixels="{TemplateBinding SnapsToDevicePixels}" 
 59                                               VerticalAlignment="{TemplateBinding VerticalContentAlignment}"  />
 60                             </Border>
 61                             <ControlTemplate.Triggers>
 62                                 <Trigger Property="IsMouseOver" Value="True">
 63                                     <Setter  TargetName="border" Property="Background" Value="#7BA0DF"/>
 64                                     <Setter Property="Foreground" Value="White"></Setter>
 65                                 </Trigger>
 66                             </ControlTemplate.Triggers>
 67                         </ControlTemplate>
 68                     </Setter.Value>
 69                 </Setter>
 70             </Style>
 71 
 72             <Style x:Key="{x:Type ListBox}"  TargetType="{x:Type ListBox}">
 73                 <Setter Property="Background" Value="White"/>
 74                 <Setter Property="ScrollViewer.HorizontalScrollBarVisibility" Value="Disabled" />
 75                 <Style.Triggers>
 76                     <Trigger Property="IsSelected" Value="True">
 77                         <Setter Property="Background" Value="#90F670"/>
 78                         <Setter Property="Foreground" Value="#90F670"/>
 79                     </Trigger>
 80                 </Style.Triggers>
 81             </Style>
 82 
 83             <Style x:Key="ColumnHeaderStyle" TargetType="{x:Type DataGrid}">
 84                 <Setter Property="HorizontalContentAlignment" Value="Center"/>
 85             </Style>
 86         </Grid.Resources>
 87 
 88         <TextBox Text="{Binding FileName}" Grid.Row="1" Grid.Column="2"  Grid.ColumnSpan="3" Margin=" 3,3,3,3"></TextBox>
 89         <Button x:Name="btnOpen"     Grid.Row="1"  Content="导入参数" Height="20" Width="104"  Command="{Binding FileBrowsCommand}" Margin="5,4,1,4" Grid.Column="1"   />
 90     
 91         <DataGrid x:Name="lbx_combinetype"  Grid.Column="1" Grid.Row="2" Grid.ColumnSpan="4" Grid.RowSpan="6" ItemsSource="{Binding LinearParam}" AutoGenerateColumns="False"  CanUserAddRows="false" GridLinesVisibility="All" Background="White" RowHeight="25" FontSize="12">
 92             
 93             <DataGrid.Columns>
 94                 <DataGridTextColumn Header="X"  Binding="{Binding xParam}" Width="*" ></DataGridTextColumn>
 95                 <DataGridTextColumn Header="Y"   Binding="{Binding yParam}" Width="*" ></DataGridTextColumn>
 96             </DataGrid.Columns>
 97         </DataGrid>
 98 
 99         <GroupBox  Grid.Row="8" Grid.Column="1"  Grid.ColumnSpan="4"  Grid.RowSpan="3" Header="模型"  Visibility="Visible">
100             <StackPanel>
101                 <Grid>
102                     <Grid.RowDefinitions>
103                         <RowDefinition Height="35"/>
104                         <RowDefinition Height="35"/>
105                     </Grid.RowDefinitions>
106                     <Grid.ColumnDefinitions>
107                         <ColumnDefinition Width="3*"/>
108                         <ColumnDefinition Width="3*"/>
109                         <ColumnDefinition Width="3*"/>
110                     </Grid.ColumnDefinitions>
111                     <RadioButton  x:Name="rb_LinearOne" Content="一次线性" Grid.Row="0" Grid.Column="0" HorizontalAlignment="Left"  VerticalAlignment="Center" Margin="34,8,33,8"  IsChecked="{Binding IsCkLinearOne}" />
112                     <RadioButton  x:Name="rb_LinearTwo" Content="二次线性" Grid.Row="0"  Grid.Column="1" HorizontalAlignment="Left"  VerticalAlignment="Center" Margin="34,5,33,5"  IsChecked="{Binding IsCkLinearTwo}" />
113                     <RadioButton  x:Name="rb_LinearE"   Grid.Row="0" Content="e的指数" Grid.Column="2" HorizontalAlignment="Left"  VerticalAlignment="Center" Margin="34,8,33,8"   IsChecked="{Binding IsCkLinearE}"   />
114                     <RadioButton  x:Name="rb_LinearPow"  Grid.Row="1" Content="幂指数" Grid.Column="0" HorizontalAlignment="Left"  VerticalAlignment="Center" Margin="34,5,33,15"  IsChecked="{Binding IsCkLinearPow}" />
115                     <RadioButton  x:Name="rb_LinearLog" Grid.Row="1" Content="对数"  Grid.Column="1" HorizontalAlignment="Left"  VerticalAlignment="Center" Margin="34,5,33,15"  IsChecked="{Binding IsCkLinearLog}" />
116                 </Grid>
117              
118             </StackPanel>
119         </GroupBox>
120 
121         <Button x:Name="btnCal"     Grid.Row="12" Grid.Column="4"  Content="计 算" Height="30" Width="100"  Command="{Binding GenerateCommand}"  >
122         </Button>
123     </Grid>
124 </window>

View Code

对应的VM代码：

  1 using System;
  2 using System.IO;
  3 using Common;
  4 using System.Collections.ObjectModel;
  5 using GalaSoft.MvvmLight.Command;
  6 using System.Data;
  7 using System.Windows.Input;
  8 using System.Windows.Forms;
  9 using OfficeHelper;
 10 using System.Collections.Generic;
 11 using Common.Control.View;
 12 
 13 namespace Common.Control.ViewModel
 14 {
 15     public class LinearFuncVM 
 16     {
 17         #region 属性
 18 
 19         private ObservableCollection<LinearPair> linearParam = new ObservableCollection<LinearPair>();
 20         public ObservableCollection<LinearPair> LinearParam
 21         {
 22             get { return linearParam; }
 23             set
 24             {
 25                 linearParam = value;
 26                 OnPropertyChanged("LinearParam");
 27             }
 28         }
 29 
 30         private string fileName = null;
 31         public string FileName
 32         {
 33             get { return fileName; }
 34             set
 35             {
 36                 fileName = value;
 37                 OnPropertyChanged("FileName");
 38             }
 39         }
 40 
 41         private string saveFile = null;
 42         /// <summary>
 43         ///  保存文件名
 44         /// </summary>
 45         public string SaveFile
 46         {
 47             get { return saveFile; }
 48             set
 49             {
 50                 saveFile = value;
 51                 OnPropertyChanged("SaveFile");
 52             }
 53         }
 54         private bool _IsCkLinearLog;
 55         public bool IsCkLinearLog
 56         {
 57             get
 58             {
 59                 return _IsCkLinearLog;
 60             }
 61             set
 62             {
 63                 _IsCkLinearLog = value;
 64                 _modelName = ModelName.LinearLog;
 65                 OnPropertyChanged("IsCkLinearLog");
 66             }
 67         }
 68         private bool _IsCkLinearPow;
 69         public bool IsCkLinearPow
 70         {
 71             get
 72             {
 73                 return _IsCkLinearPow;
 74             }
 75             set
 76             {
 77                 _IsCkLinearPow = value;
 78                 _modelName = ModelName.LinearPow;
 79                 OnPropertyChanged("IsCkLinearPow");
 80             }
 81         }
 82 
 83         private bool _IsCkLinearTwo;
 84         public bool IsCkLinearTwo
 85         {
 86             get
 87             {
 88                 return _IsCkLinearTwo;
 89             }
 90             set
 91             {
 92                 _IsCkLinearTwo = value;
 93                 _modelName = ModelName.LinearTwo;
 94                 OnPropertyChanged("IsCkLinearTwo");
 95             }
 96         }
 97 
 98         private bool _IsCkLinearE;
 99         public bool IsCkLinearE
100         {
101             get
102             {
103                 return _IsCkLinearE;
104             }
105 
106             set
107             {
108                 _IsCkLinearE = value;
109                 _modelName = ModelName.LinearE;
110                 OnPropertyChanged("IsCkLinearE");
111             }
112         }
113 
114         private bool _IsCkLinearOne;
115         public bool IsCkLinearOne
116         {
117             get
118             {
119                 return _IsCkLinearOne;
120             }
121 
122             set
123             {
124                 _IsCkLinearOne = value;
125                 _modelName = ModelName.LinearOne;
126                 OnPropertyChanged("IsCkLinearOne");
127             }
128         }
129 
130         #endregion
131         private List<double> X_data;
132         private List<double> Y_data;
133         private double[] x;
134         private double[] y;
135         private double[] yy;
136         private ModelName _modelName;
137         #region cmd 
138         public ICommand FileBrowsCommand { get; }
139         public ICommand GenerateCommand { get; }
140         #endregion
141 
142 
143         public LinearFuncVM()
144         {
145             FileBrowsCommand = new RelayCommand(OpenFile);
146             GenerateCommand = new RelayCommand(Generate);
147         }
148 
149         private void OpenFile()
150         {
151             try
152             {
153                 // 获得要打开Shape数据的路径
154                 OpenFileDialog openFileDialog = new OpenFileDialog();
155                 openFileDialog.Title = "打开Excel";
156                 openFileDialog.Filter = "Excel|*.xls;";
157                 if (openFileDialog.ShowDialog() != DialogResult.OK) return;
158                 fileName = openFileDialog.FileName;
159                 GetExcelInfo();
160                 showParamInfo(); 
161             }
162             catch (Exception ex)
163             {
164             }
165         }
166         private void GetExcelInfo()
167         {
168             try
169             {
170                 DataTable db = NPOIHelper.Instance.ImportExceltoDt(fileName);
171                 if (db == null) return;
172                 X_data = new List<double>();
173                 Y_data = new List<double>();
174                 for (int i = 0; i < db.Rows.Count; i++)
175                 {
176                     X_data.Add(Convert.ToSingle(db.Rows[i][0].ToString()));
177                     Y_data.Add(Convert.ToSingle(db.Rows[i][1].ToString()));
178                 }
179                 x = new double[Y_data.Count];
180                 y = new double[Y_data.Count];
181                 yy = new double[Y_data.Count];
182                 for (int i = 0; i < Y_data.Count; i++)
183                 {
184                     x[i] = X_data[i];
185                     y[i] = Y_data[i];
186                 }
187             }
188             catch (Exception)
189             {
190                 MessageBox.Show("表格格式错误");
191             }
192         }
193         private void Generate()
194         {
195             switch (_modelName)
196             {
197                 case ModelName.LinearZero:
198                     LinearZero();
199                     break;
200                 case ModelName.LinearOne:
201                     LinearOne();
202                     break;
203                 case ModelName.LinearTwo:
204                     LinearTwo();
205                     break;
206                 case ModelName.LinearE:
207                     LinearE();
208                     break;
209                 case ModelName.LinearPow:
210                     LinearPow();
211                     break;
212                 case ModelName.LinearLog:
213                     LinearLog();
214                     break;
215                 default:
216                     break;
217             }
218         }
219         private void LinearZero()
220         {
221             double[] ratio = FittingFunct.LinearInterceptZero(y, x);
222             for (int i = 0; i < x.Length; i++)
223             {
224                 yy[i] = ratio[0] * x[i];
225             }
226             string res = "R²=: " + FittingFunct.Pearson(y, yy);
227         }
228         private void LinearOne()
229         {      
230             double[] ratio = FittingFunct.Linear(y, x);
231             for (int i = 0; i < x.Length; i++)
232             {
233                 yy[i] = ratio[0] + ratio[1] * x[i];
234             }
235             string res = "R²=: " + FittingFunct.Pearson(y, yy);
236         }
237         private void LinearTwo()
238         {
239             double[] ratio =  FittingFunct.TowTimesCurve(y, x);
240             for (int i = 0; i < x.Length; i++)
241             {
242                 yy[i] = ratio[0] + ratio[1] * x[i] + ratio[2] * x[i] * x[i];
243             }
244             string res = "R²=: " + FittingFunct.Pearson(y, yy);
245         }
246         private void LinearE()
247         {
248             double[] ratio = FittingFunct.IndexEST(y, x);
249             for (int i = 0; i < x.Length; i++)
250             {
251                 yy[i] = ratio[0] * Math.Exp(x[i] * ratio[1]);
252             }
253             string res = "R²=: " + FittingFunct.Pearson(y, yy);
254         }
255         private void LinearPow()
256         {
257             double[] ratio = FittingFunct.PowEST(y, x);
258             for (int i = 0; i < x.Length; i++)
259             {
260                 yy[i] = ratio[0] * Math.Pow(x[i], ratio[1]);
261             }
262             string res = "R²=: " + FittingFunct.Pearson(y, yy);
263         }
264         private void LinearLog()
265         {
266             double[] ratio = FittingFunct.LOGEST(y, x);
267             for (int i = 0; i < x.Length; i++)
268             {
269                 yy[i] = ratio[1] * Math.Log10(x[i]) + ratio[0];
270             }
271             string res = "R²=: " + FittingFunct.Pearson(y, yy);
272         }
273 
274 
275 
276         /// <summary>
277         /// 参数信息显示
278         /// </summary>
279         private void showParamInfo()
280         {
281             linearParam.Clear();
282             if (X_data.Count==0|| Y_data.Count == 0)
283             {
284                 return;
285             }
286             for (int i = 0; i < X_data.Count; i++)
287             {
288                 LinearPair tp = new LinearPair() { xParam = X_data[i].ToString(), yParam = Y_data[i].ToString() };
289                 linearParam.Add(tp);
290             }
291         }
292 
293     }
294    public  class LinearPair
295     {
296         private string xparam;
297         public string xParam
298         {
299             get { return xparam; }
300             set
301             {
302                 xparam = value;
303             }
304         }
305         private string yparam;
306         public string yParam
307         {
308             get { return yparam; }
309             set
310             {
311                 yparam = value;                
312             }
313         }
314     };
315 
316     public enum ModelName
317     {
318         /// <summary>
319         /// 一次拟合(截距为0，即强制过原点)：
320         /// </summary>
321         LinearZero,
322         /// <summary>
323         /// 一次线性拟合
324         /// </summary>
325         LinearOne,
326         /// <summary>
327         /// 二次拟合
328         /// </summary>
329         LinearTwo,
330         /// <summary>
331         /// E的指数
332         /// </summary>
333         LinearE,
334         /// <summary>
335         /// 对数拟合
336         /// </summary>
337         LinearLog,
338         /// <summary>
339         /// 幂函数
340         /// </summary>
341         LinearPow
342     }
343 
344 }

View Code

Enum匹配拟合方法：

 1     public enum ModelName
 2     {
 3         /// <summary>
 4         /// 一次拟合(截距为0，即强制过原点)：
 5         /// </summary>
 6         LinearZero,
 7         /// <summary>
 8         /// 一次线性拟合
 9         /// </summary>
10         LinearOne,
11         /// <summary>
12         /// 二次拟合
13         /// </summary>
14         LinearTwo,
15         /// <summary>
16         /// E的指数
17         /// </summary>
18         LinearE,
19         /// <summary>
20         /// 对数拟合
21         /// </summary>
22         LinearLog,
23         /// <summary>
24         /// 幂函数
25         /// </summary>
26         LinearPow
27     }

ModelName

附:调用的npoi的打开excelHelp方法

   1 using System;
   2 using System.Collections.Generic;
   3 using System.Data;
   4 using System.IO;
   5 using System.Text;
   6 using NPOI.HPSF;
   7 using NPOI.HSSF.UserModel;
   8 using NPOI.SS.Formula.Eval;
   9 using NPOI.SS.UserModel;
  10 using NPOI.SS.Util;
  11 using System.Collections;
  12 using System.Text.RegularExpressions;
  13 using NPOI.XSSF.UserModel;
  14 using Common;
  15 namespace OfficeHelper
  16 {
  17     public class NPOIHelper
  18     {
  19         // private  mLogger wl = new mLogger();
  20         private static NPOIHelper s_Instance = null;
  21         public static NPOIHelper Instance
  22         {
  23             get
  24             {
  25                 if (s_Instance == null)
  26                     s_Instance = new NPOIHelper();
  27                 return s_Instance;
  28             }
  29         }
  30 
  31         #region 从datatable中将数据导出到excel
  32         /// <summary>
  33         /// DataTable导出到Excel的MemoryStream
  34         /// </summary>
  35         /// <param name="dtSource">源DataTable</param>
  36         /// <param name="strHeaderText">表头文本</param>
  37         MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
  38         {
  39             HSSFWorkbook workbook = new HSSFWorkbook();
  40             HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
  41 
  42 
  43             HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
  44             HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
  45             dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
  46 
  47             //取得列宽
  48             int[] arrColWidth = new int[dtSource.Columns.Count];
  49             foreach (DataColumn item in dtSource.Columns)
  50             {
  51                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
  52             }
  53             for (int i = 0; i < dtSource.Rows.Count; i++)
  54             {
  55                 for (int j = 0; j < dtSource.Columns.Count; j++)
  56                 {
  57                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
  58                     if (intTemp > arrColWidth[j])
  59                     {
  60                         arrColWidth[j] = intTemp;
  61                     }
  62                 }
  63             }
  64             int rowIndex = 0;
  65 
  66             foreach (DataRow row in dtSource.Rows)
  67             {
  68                 #region 新建表，填充表头，填充列头，样式
  69 
  70                 if (rowIndex == 65535 || rowIndex == 0)
  71                 {
  72                     if (rowIndex != 0)
  73                     {
  74                         sheet = workbook.CreateSheet() as HSSFSheet;
  75                     }
  76 
  77                     #region 表头及样式
  78 
  79                     {
  80                         HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
  81                         headerRow.HeightInPoints = 25;
  82                         headerRow.CreateCell(0).SetCellValue(strHeaderText);
  83 
  84                         HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
  85                         headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
  86                         HSSFFont font = workbook.CreateFont() as HSSFFont;
  87                         font.FontHeightInPoints = 20;
  88                         font.Boldweight = 700;
  89                         headStyle.SetFont(font);
  90 
  91                         headerRow.GetCell(0).CellStyle = headStyle;
  92 
  93                         sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
  94                         //headerRow.Dispose();
  95                     }
  96 
  97                     #endregion
  98 
  99 
 100                     #region 列头及样式
 101 
 102                     {
 103                         HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
 104 
 105 
 106                         HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
 107                         headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
 108                         HSSFFont font = workbook.CreateFont() as HSSFFont;
 109                         font.FontHeightInPoints = 10;
 110                         font.Boldweight = 700;
 111                         headStyle.SetFont(font);
 112 
 113 
 114                         foreach (DataColumn column in dtSource.Columns)
 115                         {
 116                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
 117                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
 118 
 119                             //设置列宽
 120                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
 121 
 122                         }
 123                         //headerRow.Dispose();
 124                     }
 125 
 126                     #endregion
 127 
 128                     rowIndex = 2;
 129                 }
 130 
 131                 #endregion
 132 
 133                 #region 填充内容
 134 
 135                 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
 136                 foreach (DataColumn column in dtSource.Columns)
 137                 {
 138                     HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
 139 
 140                     string drValue = row[column].ToString();
 141 
 142                     switch (column.DataType.ToString())
 143                     {
 144                         case "System.String": //字符串类型
 145                             double result;
 146                             if (isNumeric(drValue, out result))
 147                             {
 148 
 149                                 double.TryParse(drValue, out result);
 150                                 newCell.SetCellValue(result);
 151                                 break;
 152                             }
 153                             else
 154                             {
 155                                 newCell.SetCellValue(drValue);
 156                                 break;
 157                             }
 158 
 159                         case "System.DateTime": //日期类型
 160                             DateTime dateV;
 161                             DateTime.TryParse(drValue, out dateV);
 162                             newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));
 163                             newCell.CellStyle = dateStyle; //格式化显示
 164                             break;
 165                         case "System.Boolean": //布尔型
 166                             bool boolV = false;
 167                             bool.TryParse(drValue, out boolV);
 168                             newCell.SetCellValue(boolV);
 169                             break;
 170                         case "System.Int16": //整型
 171                         case "System.Int32":
 172                         case "System.Int64":
 173                         case "System.Byte":
 174                             int intV = 0;
 175                             int.TryParse(drValue, out intV);
 176                             newCell.SetCellValue(intV);
 177                             break;
 178                         case "System.Decimal": //浮点型
 179                         case "System.Double":
 180                             double doubV = 0;
 181                             double.TryParse(drValue, out doubV);
 182                             newCell.SetCellValue(doubV);
 183                             break;
 184                         case "System.DBNull": //空值处理
 185                             newCell.SetCellValue("");
 186                             break;
 187                         default:
 188                             newCell.SetCellValue("");
 189                             break;
 190                     }
 191 
 192                 }
 193 
 194                 #endregion
 195 
 196                 rowIndex++;
 197             }
 198             using (MemoryStream ms = new MemoryStream())
 199             {
 200                 workbook.Write(ms);
 201                 ms.Flush();
 202                 ms.Position = 0;
 203 
 204                 //sheet.Dispose();
 205                 //workbook.Dispose();
 206 
 207                 return ms;
 208             }
 209         }
 210 
 211         /// <summary>
 212         /// DataTable导出到Excel的MemoryStream
 213         /// </summary>
 214         /// <param name="dtSource">源DataTable</param>
 215         /// <param name="strHeaderText">表头文本</param>
 216         void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
 217         {
 218             XSSFWorkbook workbook = new XSSFWorkbook();
 219             XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
 220 
 221             #region 右击文件 属性信息
 222 
 223             //{
 224             //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
 225             //    dsi.Company = "http://www.yongfa365.com/";
 226             //    workbook.DocumentSummaryInformation = dsi;
 227 
 228             //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
 229             //    si.Author = "柳永法"; //填加xls文件作者信息
 230             //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
 231             //    si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
 232             //    si.Comments = "说明信息"; //填加xls文件作者信息
 233             //    si.Title = "NPOI测试"; //填加xls文件标题信息
 234             //    si.Subject = "NPOI测试Demo"; //填加文件主题信息
 235             //    si.CreateDateTime = DateTime.Now;
 236             //    workbook.SummaryInformation = si;
 237             //}
 238 
 239             #endregion
 240 
 241             XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
 242             XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
 243             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
 244 
 245             //取得列宽
 246             int[] arrColWidth = new int[dtSource.Columns.Count];
 247             foreach (DataColumn item in dtSource.Columns)
 248             {
 249                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
 250             }
 251             for (int i = 0; i < dtSource.Rows.Count; i++)
 252             {
 253                 for (int j = 0; j < dtSource.Columns.Count; j++)
 254                 {
 255                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
 256                     if (intTemp > arrColWidth[j])
 257                     {
 258                         arrColWidth[j] = intTemp;
 259                     }
 260                 }
 261             }
 262             int rowIndex = 0;
 263 
 264             foreach (DataRow row in dtSource.Rows)
 265             {
 266                 #region 新建表，填充表头，填充列头，样式
 267 
 268                 if (rowIndex == 0)
 269                 {
 270                     #region 表头及样式
 271                     //{
 272                     //    XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
 273                     //    headerRow.HeightInPoints = 25;
 274                     //    headerRow.CreateCell(0).SetCellValue(strHeaderText);
 275 
 276                     //    XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
 277                     //    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
 278                     //    XSSFFont font = workbook.CreateFont() as XSSFFont;
 279                     //    font.FontHeightInPoints = 20;
 280                     //    font.Boldweight = 700;
 281                     //    headStyle.SetFont(font);
 282 
 283                     //    headerRow.GetCell(0).CellStyle = headStyle;
 284 
 285                     //    //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
 286                     //    //headerRow.Dispose();
 287                     //}
 288 
 289                     #endregion
 290 
 291 
 292                     #region 列头及样式
 293 
 294                     {
 295                         XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
 296 
 297 
 298                         XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
 299                         headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
 300                         XSSFFont font = workbook.CreateFont() as XSSFFont;
 301                         font.FontHeightInPoints = 10;
 302                         font.Boldweight = 700;
 303                         headStyle.SetFont(font);
 304 
 305 
 306                         foreach (DataColumn column in dtSource.Columns)
 307                         {
 308                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
 309                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
 310 
 311                             //设置列宽
 312                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
 313 
 314                         }
 315                         //headerRow.Dispose();
 316                     }
 317 
 318                     #endregion
 319 
 320                     rowIndex = 1;
 321                 }
 322 
 323                 #endregion
 324 
 325                 #region 填充内容
 326 
 327                 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
 328                 foreach (DataColumn column in dtSource.Columns)
 329                 {
 330                     XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
 331 
 332                     string drValue = row[column].ToString();
 333 
 334                     switch (column.DataType.ToString())
 335                     {
 336                         case "System.String": //字符串类型
 337                             double result;
 338                             if (isNumeric(drValue, out result))
 339                             {
 340 
 341                                 double.TryParse(drValue, out result);
 342                                 newCell.SetCellValue(result);
 343                                 break;
 344                             }
 345                             else
 346                             {
 347                                 newCell.SetCellValue(drValue);
 348                                 break;
 349                             }
 350 
 351                         case "System.DateTime": //日期类型
 352                             DateTime dateV;
 353                             DateTime.TryParse(drValue, out dateV);
 354                             newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));
 355 
 356                             newCell.CellStyle = dateStyle; //格式化显示
 357                             break;
 358                         case "System.Boolean": //布尔型
 359                             bool boolV = false;
 360                             bool.TryParse(drValue, out boolV);
 361                             newCell.SetCellValue(boolV);
 362                             break;
 363                         case "System.Int16": //整型
 364                         case "System.Int32":
 365                         case "System.Int64":
 366                         case "System.Byte":
 367                             int intV = 0;
 368                             int.TryParse(drValue, out intV);
 369                             newCell.SetCellValue(intV);
 370                             break;
 371                         case "System.Decimal": //浮点型
 372                         case "System.Double":
 373                             double doubV = 0;
 374                             double.TryParse(drValue, out doubV);
 375                             newCell.SetCellValue(doubV);
 376                             break;
 377                         case "System.DBNull": //空值处理
 378                             newCell.SetCellValue("");
 379                             break;
 380                         default:
 381                             newCell.SetCellValue("");
 382                             break;
 383                     }
 384 
 385                 }
 386 
 387                 #endregion
 388 
 389                 rowIndex++;
 390             }
 391             workbook.Write(fs);
 392             fs.Close();
 393         }
 394 
 395         /// <summary>
 396         /// DataTable导出到Excel文件
 397         /// </summary>
 398         /// <param name="dtSource">源DataTable</param>
 399         /// <param name="strHeaderText">表头文本</param>
 400         /// <param name="strFileName">保存位置</param>
 401         public void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName)
 402         {
 403             string[] temp = strFileName.Split('.');
 404 
 405             if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && dtSource.Rows.Count < 65536)
 406             {
 407                 using (MemoryStream ms = ExportDT(dtSource, strHeaderText))
 408                 {
 409                     using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
 410                     {
 411                         byte[] data = ms.ToArray();
 412                         fs.Write(data, 0, data.Length);
 413                         fs.Flush();
 414                     }
 415                 }
 416             }
 417             else
 418             {
 419                 if (temp[temp.Length - 1] == "xls")
 420                     strFileName = strFileName + "x";
 421 
 422                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
 423                 {
 424                     ExportDTI(dtSource, strHeaderText, fs);
 425                 }
 426             }
 427         }
 428         #endregion
 429 
 430         #region 从excel中将数据导出到datatable
 431         /// <summary>
 432         /// 读取excel 默认第一行为标头
 433         /// </summary>
 434         /// <param name="strFileName">excel文档路径</param>
 435         /// <returns></returns>
 436         public DataTable ImportExceltoDt(string strFileName)
 437         {
 438             DataTable dt = new DataTable();
 439             IWorkbook wb;
 440             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 441             {
 442                 wb = WorkbookFactory.Create(file);
 443             }
 444             ISheet sheet = wb.GetSheetAt(0);
 445             dt = ImportDt(sheet, 0, true);
 446             return dt;
 447         }
 448 
 449         /// <summary>
 450         /// 读取Excel流到DataTable
 451         /// </summary>
 452         /// <param name="stream">Excel流</param>
 453         /// <returns>第一个sheet中的数据</returns>
 454         public DataTable ImportExceltoDt(Stream stream)
 455         {
 456             try
 457             {
 458                 DataTable dt = new DataTable();
 459                 IWorkbook wb;
 460                 using (stream)
 461                 {
 462                     wb = WorkbookFactory.Create(stream);
 463                 }
 464                 ISheet sheet = wb.GetSheetAt(0);
 465                 dt = ImportDt(sheet, 0, true);
 466                 return dt;
 467             }
 468             catch (Exception)
 469             {
 470 
 471                 throw;
 472             }
 473         }
 474 
 475         /// <summary>
 476         /// 读取Excel流到DataTable
 477         /// </summary>
 478         /// <param name="stream">Excel流</param>
 479         /// <param name="sheetName">表单名</param>
 480         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
 481         /// <returns>指定sheet中的数据</returns>
 482         public DataTable ImportExceltoDt(Stream stream, string sheetName, int HeaderRowIndex)
 483         {
 484             try
 485             {
 486                 DataTable dt = new DataTable();
 487                 IWorkbook wb;
 488                 using (stream)
 489                 {
 490                     wb = WorkbookFactory.Create(stream);
 491                 }
 492                 ISheet sheet = wb.GetSheet(sheetName);
 493                 dt = ImportDt(sheet, HeaderRowIndex, true);
 494                 return dt;
 495             }
 496             catch (Exception)
 497             {
 498 
 499                 throw;
 500             }
 501         }
 502 
 503         /// <summary>
 504         /// 读取Excel流到DataSet
 505         /// </summary>
 506         /// <param name="stream">Excel流</param>
 507         /// <returns>Excel中的数据</returns>
 508         public DataSet ImportExceltoDs(Stream stream)
 509         {
 510             try
 511             {
 512                 DataSet ds = new DataSet();
 513                 IWorkbook wb;
 514                 using (stream)
 515                 {
 516                     wb = WorkbookFactory.Create(stream);
 517                 }
 518                 for (int i = 0; i < wb.NumberOfSheets; i++)
 519                 {
 520                     DataTable dt = new DataTable();
 521                     ISheet sheet = wb.GetSheetAt(i);
 522                     dt = ImportDt(sheet, 0, true);
 523                     ds.Tables.Add(dt);
 524                 }
 525                 return ds;
 526             }
 527             catch (Exception)
 528             {
 529 
 530                 throw;
 531             }
 532         }
 533 
 534         /// <summary>
 535         /// 读取Excel流到DataSet
 536         /// </summary>
 537         /// <param name="stream">Excel流</param>
 538         /// <param name="dict">字典参数，key：sheet名，value：列头所在行号，-1表示没有列头</param>
 539         /// <returns>Excel中的数据</returns>
 540         public DataSet ImportExceltoDs(Stream stream, Dictionary<string, int> dict)
 541         {
 542             try
 543             {
 544                 DataSet ds = new DataSet();
 545                 IWorkbook wb;
 546                 using (stream)
 547                 {
 548                     wb = WorkbookFactory.Create(stream);
 549                 }
 550                 foreach (string key in dict.Keys)
 551                 {
 552                     DataTable dt = new DataTable();
 553                     ISheet sheet = wb.GetSheet(key);
 554                     dt = ImportDt(sheet, dict[key], true);
 555                     ds.Tables.Add(dt);
 556                 }
 557                 return ds;
 558             }
 559             catch (Exception)
 560             {
 561 
 562                 throw;
 563             }
 564         }
 565 
 566         /// <summary>
 567         /// 读取excel
 568         /// </summary>
 569         /// <param name="strFileName">excel文件路径</param>
 570         /// <param name="sheet">需要导出的sheet</param>
 571         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
 572         /// <returns></returns>
 573         public DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex)
 574         {
 575             HSSFWorkbook workbook;
 576             IWorkbook wb;
 577             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 578             {
 579                 wb = new HSSFWorkbook(file);
 580             }
 581             ISheet sheet = wb.GetSheet(SheetName);
 582             DataTable table = new DataTable();
 583             table = ImportDt(sheet, HeaderRowIndex, true);
 584             //ExcelFileStream.Close();
 585             workbook = null;
 586             sheet = null;
 587             return table;
 588         }
 589 
 590         /// <summary>
 591         /// 读取excel
 592         /// </summary>
 593         /// <param name="strFileName">excel文件路径</param>
 594         /// <param name="sheet">需要导出的sheet序号</param>
 595         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
 596         /// <returns></returns>
 597         public DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex)
 598         {
 599             HSSFWorkbook workbook;
 600             IWorkbook wb;
 601             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 602             {
 603                 wb = WorkbookFactory.Create(file);
 604             }
 605             ISheet isheet = wb.GetSheetAt(SheetIndex);
 606             DataTable table = new DataTable();
 607             table = ImportDt(isheet, HeaderRowIndex, true);
 608             //ExcelFileStream.Close();
 609             workbook = null;
 610             isheet = null;
 611             return table;
 612         }
 613 
 614         /// <summary>
 615         /// 读取excel
 616         /// </summary>
 617         /// <param name="strFileName">excel文件路径</param>
 618         /// <param name="sheet">需要导出的sheet</param>
 619         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
 620         /// <returns></returns>
 621         public DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
 622         {
 623             HSSFWorkbook workbook;
 624             IWorkbook wb;
 625             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 626             {
 627                 wb = WorkbookFactory.Create(file);
 628             }
 629             ISheet sheet = wb.GetSheet(SheetName);
 630             DataTable table = new DataTable();
 631             table = ImportDt(sheet, HeaderRowIndex, needHeader);
 632             //ExcelFileStream.Close();
 633             workbook = null;
 634             sheet = null;
 635             return table;
 636         }
 637 
 638         /// <summary>
 639         /// 读取excel
 640         /// </summary>
 641         /// <param name="strFileName">excel文件路径</param>
 642         /// <param name="sheet">需要导出的sheet序号</param>
 643         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
 644         /// <returns></returns>
 645         public DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
 646         {
 647             HSSFWorkbook workbook;
 648             IWorkbook wb;
 649             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 650             {
 651                 wb = WorkbookFactory.Create(file);
 652             }
 653             ISheet sheet = wb.GetSheetAt(SheetIndex);
 654             DataTable table = new DataTable();
 655             table = ImportDt(sheet, HeaderRowIndex, needHeader);
 656             //ExcelFileStream.Close();
 657             workbook = null;
 658             sheet = null;
 659             return table;
 660         }
 661 
 662         /// <summary>
 663         /// 将制定sheet中的数据导出到datatable中
 664         /// </summary>
 665         /// <param name="sheet">需要导出的sheet</param>
 666         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
 667         /// <returns></returns>
 668         DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
 669         {
 670             DataTable table = new DataTable();
 671             IRow headerRow;
 672             int cellCount;
 673             try
 674             {
 675                 if (HeaderRowIndex < 0 || !needHeader)
 676                 {
 677                     headerRow = sheet.GetRow(0);
 678                     cellCount = headerRow.LastCellNum;
 679 
 680                     for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
 681                     {
 682                         DataColumn column = new DataColumn(Convert.ToString(i));
 683                         table.Columns.Add(column);
 684                     }
 685                 }
 686                 else
 687                 {
 688                     headerRow = sheet.GetRow(HeaderRowIndex);
 689                     cellCount = headerRow.LastCellNum;
 690 
 691                     for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
 692                     {
 693                         if (headerRow.GetCell(i) == null)
 694                         {
 695                             if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
 696                             {
 697                                 DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
 698                                 table.Columns.Add(column);
 699                             }
 700                             else
 701                             {
 702                                 DataColumn column = new DataColumn(Convert.ToString(i));
 703                                 table.Columns.Add(column);
 704                             }
 705 
 706                         }
 707                         else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
 708                         {
 709                             DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
 710                             table.Columns.Add(column);
 711                         }
 712                         else
 713                         {
 714                             DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
 715                             table.Columns.Add(column);
 716                         }
 717                     }
 718                 }
 719                 int rowCount = sheet.LastRowNum;
 720                 for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
 721                 {
 722                     try
 723                     {
 724                         IRow row;
 725                         if (sheet.GetRow(i) == null)
 726                         {
 727                             row = sheet.CreateRow(i);
 728                         }
 729                         else
 730                         {
 731                             row = sheet.GetRow(i);
 732                         }
 733 
 734                         DataRow dataRow = table.NewRow();
 735 
 736                         for (int j = row.FirstCellNum; j <= cellCount; j++)
 737                         {
 738                             try
 739                             {
 740                                 if (row.GetCell(j) != null)
 741                                 {
 742                                     switch (row.GetCell(j).CellType)
 743                                     {
 744                                         case CellType.String:
 745                                             string str = row.GetCell(j).StringCellValue;
 746                                             if (str != null && str.Length > 0)
 747                                             {
 748                                                 dataRow[j] = str.ToString();
 749                                             }
 750                                             else
 751                                             {
 752                                                 dataRow[j] = null;
 753                                             }
 754                                             break;
 755                                         case CellType.Numeric:
 756                                             if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
 757                                             {
 758                                                 dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
 759                                             }
 760                                             else
 761                                             {
 762                                                 dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
 763                                             }
 764                                             break;
 765                                         case CellType.Boolean:
 766                                             dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
 767                                             break;
 768                                         case CellType.Error:
 769                                             dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
 770                                             break;
 771                                         case CellType.Formula:
 772                                             switch (row.GetCell(j).CachedFormulaResultType)
 773                                             {
 774                                                 case CellType.String:
 775                                                     string strFORMULA = row.GetCell(j).StringCellValue;
 776                                                     if (strFORMULA != null && strFORMULA.Length > 0)
 777                                                     {
 778                                                         dataRow[j] = strFORMULA.ToString();
 779                                                     }
 780                                                     else
 781                                                     {
 782                                                         dataRow[j] = null;
 783                                                     }
 784                                                     break;
 785                                                 case CellType.Numeric:
 786                                                     dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
 787                                                     break;
 788                                                 case CellType.Boolean:
 789                                                     dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
 790                                                     break;
 791                                                 case CellType.Error:
 792                                                     dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
 793                                                     break;
 794                                                 default:
 795                                                     dataRow[j] = "";
 796                                                     break;
 797                                             }
 798                                             break;
 799                                         default:
 800                                             dataRow[j] = "";
 801                                             break;
 802                                     }
 803                                 }
 804                             }
 805                             catch (Exception exception)
 806                             {
 807                                 mLogger.Error(exception.ToString());
 808                             }
 809                         }
 810                         table.Rows.Add(dataRow);
 811                     }
 812                     catch (Exception exception)
 813                     {
 814                         mLogger.Error(exception.ToString());
 815                     }
 816                 }
 817             }
 818             catch (Exception exception)
 819             {
 820                 mLogger.Error(exception.ToString());
 821             }
 822             return table;
 823         }
 824 
 825         #endregion
 826 
 827 
 828         public void InsertSheet(string outputFile, string sheetname, DataTable dt)
 829         {
 830             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
 831             IWorkbook hssfworkbook = WorkbookFactory.Create(readfile);
 832             //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
 833             int num = hssfworkbook.GetSheetIndex(sheetname);
 834             ISheet sheet1;
 835             if (num >= 0)
 836                 sheet1 = hssfworkbook.GetSheet(sheetname);
 837             else
 838             {
 839                 sheet1 = hssfworkbook.CreateSheet(sheetname);
 840             }
 841 
 842 
 843             try
 844             {
 845                 if (sheet1.GetRow(0) == null)
 846                 {
 847                     sheet1.CreateRow(0);
 848                 }
 849                 for (int coluid = 0; coluid < dt.Columns.Count; coluid++)
 850                 {
 851                     if (sheet1.GetRow(0).GetCell(coluid) == null)
 852                     {
 853                         sheet1.GetRow(0).CreateCell(coluid);
 854                     }
 855 
 856                     sheet1.GetRow(0).GetCell(coluid).SetCellValue(dt.Columns[coluid].ColumnName);
 857                 }
 858             }
 859             catch (Exception ex)
 860             {
 861                 mLogger.Error(ex.ToString());
 862                 throw;
 863             }
 864 
 865 
 866             for (int i = 1; i <= dt.Rows.Count; i++)
 867             {
 868                 try
 869                 {
 870                     if (sheet1.GetRow(i) == null)
 871                     {
 872                         sheet1.CreateRow(i);
 873                     }
 874                     for (int coluid = 0; coluid < dt.Columns.Count; coluid++)
 875                     {
 876                         if (sheet1.GetRow(i).GetCell(coluid) == null)
 877                         {
 878                             sheet1.GetRow(i).CreateCell(coluid);
 879                         }
 880 
 881                         sheet1.GetRow(i).GetCell(coluid).SetCellValue(dt.Rows[i - 1][coluid].ToString());
 882                     }
 883                 }
 884                 catch (Exception ex)
 885                 {
 886                     mLogger.Error(ex.ToString());
 887                     //throw;
 888                 }
 889             }
 890             try
 891             {
 892                 readfile.Close();
 893 
 894                 FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);
 895                 hssfworkbook.Write(writefile);
 896                 writefile.Close();
 897             }
 898             catch (Exception ex)
 899             {
 900                 mLogger.Error(ex.ToString());
 901             }
 902         }
 903 
 904         #region 更新excel中的数据(xls)
 905         /// <summary>
 906         /// 更新Excel表格
 907         /// </summary>
 908         /// <param name="outputFile">需更新的excel表格路径</param>
 909         /// <param name="sheetname">sheet名</param>
 910         /// <param name="updateData">需更新的数据</param>
 911         /// <param name="coluid">需更新的列号</param>
 912         /// <param name="rowid">需更新的开始行号</param>
 913         public void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)
 914         {
 915             try
 916             {
 917                 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
 918                 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
 919                 ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
 920                 for (int i = 0; i < updateData.Length; i++)
 921                 {
 922                     try
 923                     {
 924                         if (sheet1.GetRow(i + rowid) == null)
 925                         {
 926                             sheet1.CreateRow(i + rowid);
 927                         }
 928                         if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
 929                         {
 930                             sheet1.GetRow(i + rowid).CreateCell(coluid);
 931                         }
 932 
 933                         sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
 934                     }
 935                     catch (Exception ex)
 936                     {
 937                         mLogger.Error(ex.ToString());
 938                         throw;
 939                     }
 940                 }
 941                 try
 942                 {
 943                     //readfile.Close();
 944                     FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);
 945                     hssfworkbook.Write(writefile);
 946                     writefile.Close();
 947                 }
 948                 catch (Exception ex)
 949                 {
 950                     mLogger.Error(ex.ToString());
 951                 }
 952             }
 953             catch (Exception ex)
 954             {
 955 
 956                 throw;
 957             }
 958 
 959 
 960         }
 961 
 962         /// <summary>
 963         /// 更新Excel表格
 964         /// </summary>
 965         /// <param name="outputFile">需更新的excel表格路径</param>
 966         /// <param name="sheetname">sheet名</param>
 967         /// <param name="updateData">需更新的数据</param>
 968         /// <param name="coluids">需更新的列号</param>
 969         /// <param name="rowid">需更新的开始行号</param>
 970         public void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
 971         {
 972             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
 973 
 974             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
 975             readfile.Close();
 976             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
 977             for (int j = 0; j < coluids.Length; j++)
 978             {
 979                 for (int i = 0; i < updateData[j].Length; i++)
 980                 {
 981                     try
 982                     {
 983                         if (sheet1.GetRow(i + rowid) == null)
 984                         {
 985                             sheet1.CreateRow(i + rowid);
 986                         }
 987                         if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
 988                         {
 989                             sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
 990                         }
 991                         sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
 992                     }
 993                     catch (Exception ex)
 994                     {
 995                         mLogger.Error(ex.ToString());
 996                     }
 997                 }
 998             }
 999             try
1000             {
1001                 FileStream writefile = new FileStream(outputFile, FileMode.Create);
1002                 hssfworkbook.Write(writefile);
1003                 writefile.Close();
1004             }
1005             catch (Exception ex)
1006             {
1007                 mLogger.Error(ex.ToString());
1008             }
1009         }
1010 
1011         /// <summary>
1012         /// 更新Excel表格
1013         /// </summary>
1014         /// <param name="outputFile">需更新的excel表格路径</param>
1015         /// <param name="sheetname">sheet名</param>
1016         /// <param name="updateData">需更新的数据</param>
1017         /// <param name="coluids">需更新的列号</param>
1018         /// <param name="rowid">需更新的开始行号</param>
1019         public void UpdateExcelRow(string outputFile, string sheetname, string[] updateData, int col_id, int rowid)
1020         {
1021             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1022 
1023             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1024             readfile.Close();
1025             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
1026 
1027             for (int i = 0; i < updateData.Length; i++)
1028             {
1029                 try
1030                 {
1031                     if (sheet1.GetRow(rowid) == null)
1032                     {
1033                         sheet1.CreateRow(rowid);
1034                     }
1035                     if (sheet1.GetRow(rowid).GetCell(col_id + i) == null)
1036                     {
1037                         sheet1.GetRow(rowid).CreateCell(col_id + i);
1038                     }
1039                     sheet1.GetRow(rowid).GetCell(col_id + i).SetCellValue(updateData[i]);
1040                 }
1041                 catch (Exception ex)
1042                 {
1043                     mLogger.Error(ex.ToString());
1044                 }
1045             }
1046             try
1047             {
1048                 FileStream writefile = new FileStream(outputFile, FileMode.Create);
1049                 hssfworkbook.Write(writefile);
1050                 writefile.Close();
1051             }
1052             catch (Exception ex)
1053             {
1054                 mLogger.Error(ex.ToString());
1055             }
1056         }
1057 
1058         /// <summary>
1059         /// 更新Excel表格
1060         /// </summary>
1061         /// <param name="outputFile">需更新的excel表格路径</param>
1062         /// <param name="sheetname">sheet名</param>
1063         /// <param name="updateData">需更新的数据</param>
1064         /// <param name="coluid">需更新的列号</param>
1065         /// <param name="rowid">需更新的开始行号</param>
1066         public void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
1067         {
1068             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1069 
1070             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1071             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
1072             for (int i = 0; i < updateData.Length; i++)
1073             {
1074                 try
1075                 {
1076                     if (sheet1.GetRow(i + rowid) == null)
1077                     {
1078                         sheet1.CreateRow(i + rowid);
1079                     }
1080                     if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
1081                     {
1082                         sheet1.GetRow(i + rowid).CreateCell(coluid);
1083                     }
1084 
1085                     sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
1086                 }
1087                 catch (Exception ex)
1088                 {
1089                     mLogger.Error(ex.ToString());
1090                     throw;
1091                 }
1092             }
1093             try
1094             {
1095                 readfile.Close();
1096                 FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
1097                 hssfworkbook.Write(writefile);
1098                 writefile.Close();
1099             }
1100             catch (Exception ex)
1101             {
1102                 mLogger.Error(ex.ToString());
1103             }
1104 
1105         }
1106 
1107         /// <summary>
1108         /// 更新Excel表格
1109         /// </summary>
1110         /// <param name="outputFile">需更新的excel表格路径</param>
1111         /// <param name="sheetname">sheet名</param>
1112         /// <param name="updateData">需更新的数据</param>
1113         /// <param name="coluids">需更新的列号</param>
1114         /// <param name="rowid">需更新的开始行号</param>
1115         public void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
1116         {
1117             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1118 
1119             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1120             readfile.Close();
1121             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
1122             for (int j = 0; j < coluids.Length; j++)
1123             {
1124                 for (int i = 0; i < updateData[j].Length; i++)
1125                 {
1126                     try
1127                     {
1128                         if (sheet1.GetRow(i + rowid) == null)
1129                         {
1130                             sheet1.CreateRow(i + rowid);
1131                         }
1132                         if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
1133                         {
1134                             sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
1135                         }
1136                         sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
1137                     }
1138                     catch (Exception ex)
1139                     {
1140                         mLogger.Error(ex.ToString());
1141                     }
1142                 }
1143             }
1144             try
1145             {
1146                 FileStream writefile = new FileStream(outputFile, FileMode.Create);
1147                 hssfworkbook.Write(writefile);
1148                 writefile.Close();
1149             }
1150             catch (Exception ex)
1151             {
1152                 mLogger.Error(ex.ToString());
1153             }
1154         }
1155 
1156         #endregion
1157 
1158 
1159         public int GetSheetNumber(string outputFile)
1160         {
1161             int number = 0;
1162             try
1163             {
1164                 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1165 
1166                 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1167                 number = hssfworkbook.NumberOfSheets;
1168 
1169             }
1170             catch (Exception exception)
1171             {
1172                 mLogger.Error(exception.ToString());
1173             }
1174             return number;
1175         }
1176 
1177         public ArrayList GetSheetName(string outputFile)
1178         {
1179             ArrayList arrayList = new ArrayList();
1180             try
1181             {
1182                 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1183 
1184                 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1185                 for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
1186                 {
1187                     arrayList.Add(hssfworkbook.GetSheetName(i));
1188                 }
1189             }
1190             catch (Exception exception)
1191             {
1192                 mLogger.Error(exception.ToString());
1193             }
1194             return arrayList;
1195         }
1196 
1197         public bool isNumeric(String message, out double result)
1198         {
1199             Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
1200             result = -1;
1201             if (rex.IsMatch(message))
1202             {
1203                 result = double.Parse(message);
1204                 return true;
1205             }
1206             else
1207                 return false;
1208 
1209         }
1210 
1211 
1212         /// <summary>
1213         /// 获取sheet表对应的DataTable
1214         /// </summary>
1215         /// <param name="sheet">Excel工作表</param>
1216         /// <param name="strMsg"></param>
1217         /// <returns></returns>
1218         public DataTable GetSheetDataTable(ISheet sheet, string strMsg)
1219         {
1220             strMsg = "";
1221             DataTable dt = new DataTable();
1222             string sheetName = sheet.SheetName;
1223             int startIndex = 0;// sheet.FirstRowNum;
1224             int lastIndex = sheet.LastRowNum;
1225             //最大列数
1226             int cellCount = 0;
1227             IRow maxRow = sheet.GetRow(0);
1228             for (int i = startIndex; i <= lastIndex; i++)
1229             {
1230                 IRow row = sheet.GetRow(i);
1231                 if (row != null && cellCount < row.LastCellNum)
1232                 {
1233                     cellCount = row.LastCellNum;
1234                     maxRow = row;
1235                 }
1236             }
1237             //列名设置
1238             try
1239             {
1240                 for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum
1241                 {
1242                     dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
1243                     //DataColumn column = new DataColumn("Column" + (i + 1).ToString());
1244                     //dt.Columns.Add(column);
1245                 }
1246             }
1247             catch
1248             {
1249                 strMsg = "工作表" + sheetName + "中无数据";
1250                 return null;
1251             }
1252             //数据填充
1253             for (int i = startIndex; i <= lastIndex; i++)
1254             {
1255                 IRow row = sheet.GetRow(i);
1256                 DataRow drNew = dt.NewRow();
1257                 if (row != null)
1258                 {
1259                     for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
1260                     {
1261                         if (row.GetCell(j) != null)
1262                         {
1263                             ICell cell = row.GetCell(j);
1264                             switch (cell.CellType)
1265                             {
1266                                 case CellType.Blank:
1267                                     drNew[j] = "";
1268                                     break;
1269                                 case CellType.Numeric:
1270                                     short format = cell.CellStyle.DataFormat;
1271                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
1272                                     if (format == 14 || format == 31 || format == 57 || format == 58)
1273                                         drNew[j] = cell.DateCellValue;
1274                                     else
1275                                         drNew[j] = cell.NumericCellValue;
1276                                     if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
1277                                         drNew[j] = cell.NumericCellValue.ToString("#0.00");
1278                                     break;
1279                                 case CellType.String:
1280                                     drNew[j] = cell.StringCellValue;
1281                                     break;
1282                                 case CellType.Formula:
1283                                     try
1284                                     {
1285                                         drNew[j] = cell.NumericCellValue;
1286                                         if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
1287                                             drNew[j] = cell.NumericCellValue.ToString("#0.00");
1288                                     }
1289                                     catch
1290                                     {
1291                                         try
1292                                         {
1293                                             drNew[j] = cell.StringCellValue;
1294                                         }
1295                                         catch { }
1296                                     }
1297                                     break;
1298                                 default:
1299                                     drNew[j] = cell.StringCellValue;
1300                                     break;
1301                             }
1302                         }
1303                     }
1304                 }
1305                 dt.Rows.Add(drNew);
1306             }
1307             return dt;
1308         }
1309 
1310 
1311 
1312         /// <summary>
1313         /// DataTable导出到Excel的MemoryStream                                                                      第二步
1314         /// </summary>
1315         /// <param name="dtSource">源DataTable</param>
1316         /// <param name="strHeaderText">表头文本</param>
1317         public MemoryStream Export(DataTable dtSource, string strHeaderText)
1318         {
1319             HSSFWorkbook workbook = new HSSFWorkbook();
1320             HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
1321 
1322             #region 右击文件 属性信息
1323             {
1324                 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
1325                 dsi.Company = "NPOI";
1326                 workbook.DocumentSummaryInformation = dsi;
1327 
1328                 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
1329                 si.Author = "文件作者信息"; //填加xls文件作者信息
1330                 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
1331                 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
1332                 si.Comments = "作者信息"; //填加xls文件作者信息
1333                 si.Title = "标题信息"; //填加xls文件标题信息
1334                 si.Subject = "主题信息";//填加文件主题信息
1335 
1336                 si.CreateDateTime = DateTime.Now;
1337                 workbook.SummaryInformation = si;
1338             }
1339             #endregion
1340 
1341             HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
1342             HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
1343             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
1344 
1345             //取得列宽
1346             int[] arrColWidth = new int[dtSource.Columns.Count];
1347             foreach (DataColumn item in dtSource.Columns)
1348             {
1349                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
1350             }
1351             for (int i = 0; i < dtSource.Rows.Count; i++)
1352             {
1353                 for (int j = 0; j < dtSource.Columns.Count; j++)
1354                 {
1355                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
1356                     if (intTemp > arrColWidth[j])
1357                     {
1358                         arrColWidth[j] = intTemp;
1359                     }
1360                 }
1361             }
1362             int rowIndex = 0;
1363             foreach (DataRow row in dtSource.Rows)
1364             {
1365                 #region 新建表，填充表头，填充列头，样式
1366                 if (rowIndex == 65535 || rowIndex == 0)
1367                 {
1368                     if (rowIndex != 0)
1369                     {
1370                         sheet = workbook.CreateSheet() as HSSFSheet;
1371                     }
1372 
1373                     #region 表头及样式
1374                     {
1375                         if (string.IsNullOrEmpty(strHeaderText))
1376                         {
1377                             HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
1378                             headerRow.HeightInPoints = 25;
1379                             headerRow.CreateCell(0).SetCellValue(strHeaderText);
1380                             HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
1381                             //headStyle.Alignment = CellHorizontalAlignment.CENTER;
1382                             HSSFFont font = workbook.CreateFont() as HSSFFont;
1383                             font.FontHeightInPoints = 20;
1384                             font.Boldweight = 700;
1385                             headStyle.SetFont(font);
1386                             headerRow.GetCell(0).CellStyle = headStyle;
1387                             sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
1388                             //headerRow.Dispose();
1389                         }
1390                     }
1391                     #endregion
1392 
1393                     #region 列头及样式
1394                     {
1395                         HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
1396                         HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
1397                         //headStyle.Alignment = CellHorizontalAlignment.CENTER;
1398                         HSSFFont font = workbook.CreateFont() as HSSFFont;
1399                         font.FontHeightInPoints = 10;
1400                         font.Boldweight = 700;
1401                         headStyle.SetFont(font);
1402                         foreach (DataColumn column in dtSource.Columns)
1403                         {
1404                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
1405                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
1406 
1407                             //设置列宽
1408                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
1409                         }
1410                         //headerRow.Dispose();
1411                     }
1412                     #endregion
1413 
1414                     rowIndex = 1;
1415                 }
1416                 #endregion
1417 
1418 
1419                 #region 填充内容
1420                 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
1421                 foreach (DataColumn column in dtSource.Columns)
1422                 {
1423                     HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
1424 
1425                     string drValue = row[column].ToString();
1426 
1427                     switch (column.DataType.ToString())
1428                     {
1429                         case "System.String"://字符串类型
1430                             newCell.SetCellValue(drValue);
1431                             break;
1432                         case "System.DateTime"://日期类型
1433                             DateTime dateV;
1434                             DateTime.TryParse(drValue, out dateV);
1435                             newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));
1436 
1437                             newCell.CellStyle = dateStyle;//格式化显示
1438                             break;
1439                         case "System.Boolean"://布尔型
1440                             bool boolV = false;
1441                             bool.TryParse(drValue, out boolV);
1442                             newCell.SetCellValue(boolV);
1443                             break;
1444                         case "System.Int16"://整型
1445                         case "System.Int32":
1446                         case "System.Int64":
1447                         case "System.Byte":
1448                             int intV = 0;
1449                             int.TryParse(drValue, out intV);
1450                             newCell.SetCellValue(intV);
1451                             break;
1452                         case "System.Decimal"://浮点型
1453                         case "System.Double":
1454                             double doubV = 0;
1455                             double.TryParse(drValue, out doubV);
1456                             newCell.SetCellValue(doubV);
1457                             break;
1458                         case "System.DBNull"://空值处理
1459                             newCell.SetCellValue("");
1460                             break;
1461                         default:
1462                             newCell.SetCellValue("");
1463                             break;
1464                     }
1465                 }
1466                 #endregion
1467 
1468                 rowIndex++;
1469             }
1470             using (MemoryStream ms = new MemoryStream())
1471             {
1472                 workbook.Write(ms);
1473                 ms.Flush();
1474                 ms.Position = 0;
1475 
1476                 //sheet.Dispose();
1477                 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
1478                 return ms;
1479             }
1480         }
1481 
1482 
1483         /// <summary>
1484         /// 由DataSet导出Excel
1485         /// </summary>
1486         /// <param name="sourceTable">要导出数据的DataTable</param>
1487         /// <param name="sheetName">工作表名称</param>
1488         /// <returns>Excel工作表</returns>
1489         private MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
1490         {
1491             HSSFWorkbook workbook = new HSSFWorkbook();
1492             MemoryStream ms = new MemoryStream();
1493             string[] sheetNames = sheetName.Split(',');
1494             for (int i = 0; i < sheetNames.Length; i++)
1495             {
1496                 ISheet sheet = workbook.CreateSheet(sheetNames[i]);
1497 
1498                 #region 列头
1499                 IRow headerRow = sheet.CreateRow(0);
1500                 HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
1501                 HSSFFont font = workbook.CreateFont() as HSSFFont;
1502                 font.FontHeightInPoints = 10;
1503                 font.Boldweight = 700;
1504                 headStyle.SetFont(font);
1505 
1506                 //取得列宽
1507                 int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];
1508                 foreach (DataColumn item in sourceDs.Tables[i].Columns)
1509                 {
1510                     arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
1511                 }
1512 
1513                 // 处理列头
1514                 foreach (DataColumn column in sourceDs.Tables[i].Columns)
1515                 {
1516                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
1517                     headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
1518                     //设置列宽
1519                     sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
1520 
1521                 }
1522                 #endregion
1523 
1524                 #region 填充值
1525                 int rowIndex = 1;
1526                 foreach (DataRow row in sourceDs.Tables[i].Rows)
1527                 {
1528                     IRow dataRow = sheet.CreateRow(rowIndex);
1529                     foreach (DataColumn column in sourceDs.Tables[i].Columns)
1530                     {
1531                         dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
1532                     }
1533                     rowIndex++;
1534                 }
1535                 #endregion
1536             }
1537             workbook.Write(ms);
1538             ms.Flush();
1539             ms.Position = 0;
1540             workbook = null;
1541             return ms;
1542         }
1543 
1544 
1545         /// <summary>
1546         /// 验证导入的Excel是否有数据
1547         /// </summary>
1548         /// <param name="excelFileStream"></param>
1549         /// <returns></returns>
1550         public bool HasData(Stream excelFileStream)
1551         {
1552             using (excelFileStream)
1553             {
1554                 IWorkbook workBook = new HSSFWorkbook(excelFileStream);
1555                 if (workBook.NumberOfSheets > 0)
1556                 {
1557                     ISheet sheet = workBook.GetSheetAt(0);
1558                     return sheet.PhysicalNumberOfRows > 0;
1559                 }
1560             }
1561             return false;
1562         }
1563     }
1564 }

View Code

附参数拟合的算法类

  1 using System;
  2 using System.Collections.Generic;
  3 
  4 namespace Common
  5 {
  6     /// <summary>
  7     /// 参数拟合
  8     /// </summary>
  9     public class FittingFunct
 10     {
 11         #region 多项式拟合函数,输出系数是y=a0+a1*x+a2*x*x+.........，按a0,a1,a2输出
 12         static public double[] Polyfit(double[] y, double[] x, int order)
 13         {
 14             double[,] guass = Get_Array(y, x, order);
 15 
 16             double[] ratio = Cal_Guass(guass, order + 1);
 17 
 18             return ratio;
 19         }
 20         #endregion
 21 
 22         #region 一次拟合函数，y=a0+a1*x,输出次序是a0,a1
 23         static public double[] Linear(double[] y, double[] x)
 24         {
 25             double[] ratio = Polyfit(y, x, 1);
 26             return ratio;
 27         }
 28         #endregion
 29 
 30         #region 一次拟合函数，截距为0，y=a0x,输出次序是a0
 31         static public double[] LinearInterceptZero(double[] y, double[] x)
 32         {
 33             double divisor = 0; //除数
 34             double dividend = 0; //被除数
 35             for (int i = 0; i < x.Length; i++)
 36             {
 37                 divisor += x[i] * x[i];
 38                 dividend += x[i] * y[i];
 39             }
 40             if (divisor == 0)
 41             {
 42                 throw (new Exception("除数不为0！"));
 43             }
 44             return new double[] { dividend / divisor };
 45 
 46         }
 47         #endregion
 48 
 49         #region 二次拟合函数，y=a0+a1*x+a2x²,输出次序是a0,a1,a2
 50         static public double[] TowTimesCurve(double[] y, double[] x)
 51         {
 52             double[] ratio = Polyfit(y, x, 2);
 53             return ratio;
 54         }
 55         #endregion
 56 
 57         #region 对数拟合函数,.y= c*(ln x)+b,输出为b,c
 58         static public double[] LOGEST(double[] y, double[] x)
 59         {
 60             double[] lnX = new double[x.Length];
 61 
 62             for (int i = 0; i < x.Length; i++)
 63             {
 64                 if (x[i] == 0 || x[i] < 0)
 65                 {
 66                     throw (new Exception("正对非正数取对数！"));
 67                 }
 68                 lnX[i] = Math.Log(x[i]);
 69             }
 70 
 71             return Linear(y, lnX);
 72         }
 73         #endregion
 74 
 75         #region 幂函数拟合模型, y=c*x^b,输出为c,b
 76         static public double[] PowEST(double[] y, double[] x)
 77         {
 78             double[] lnX = new double[x.Length];
 79             double[] lnY = new double[y.Length];
 80             double[] dlinestRet;
 81 
 82             for (int i = 0; i < x.Length; i++)
 83             {
 84                 lnX[i] = Math.Log(x[i]);
 85                 lnY[i] = Math.Log(y[i]);
 86             }
 87 
 88             dlinestRet = Linear(lnY, lnX);
 89 
 90             dlinestRet[0] = Math.Exp(dlinestRet[0]);
 91 
 92             return dlinestRet;
 93         }
 94         #endregion
 95 
 96         #region 指数函数拟合函数模型，公式为 y=c*m^x;输出为 c,m
 97         static public double[] IndexEST(double[] y, double[] x)
 98         {
 99             double[] lnY = new double[y.Length];
100             double[] ratio;
101             for (int i = 0; i < y.Length; i++)
102             {
103                 lnY[i] = Math.Log(y[i]);
104             }
105 
106             ratio = Linear(lnY, x);
107             for (int i = 0; i < ratio.Length; i++)
108             {
109                 if (i == 0)
110                 {
111                     ratio[i] = Math.Exp(ratio[i]);
112                 }
113             }
114             return ratio;
115         }
116         #endregion
117 
118         #region 相关系数R²部分
119         public static double Pearson(IEnumerable<double> dataA, IEnumerable<double> dataB)
120         {
121             int n = 0;
122             double r = 0.0;
123 
124             double meanA = 0;
125             double meanB = 0;
126             double varA = 0;
127             double varB = 0;
128             int ii = 0;
129             using (IEnumerator<double> ieA = dataA.GetEnumerator())
130             using (IEnumerator<double> ieB = dataB.GetEnumerator())
131             {
132                 while (ieA.MoveNext())
133                 {
134                     if (!ieB.MoveNext())
135                     {
136                         //throw new ArgumentOutOfRangeException("dataB", Resources.ArgumentArraysSameLength);
137                     }
138                     ii++;
139                     //Console.WriteLine("FF00::  " + ii + " --  " + meanA + " -- " + meanB + " -- " + varA + "  ---  " + varB);
140                     double currentA = ieA.Current;
141                     double currentB = ieB.Current;
142 
143                     double deltaA = currentA - meanA;
144                     double scaleDeltaA = deltaA / ++n;
145 
146                     double deltaB = currentB - meanB;
147                     double scaleDeltaB = deltaB / n;
148 
149                     meanA += scaleDeltaA;
150                     meanB += scaleDeltaB;
151 
152                     varA += scaleDeltaA * deltaA * (n - 1);
153                     varB += scaleDeltaB * deltaB * (n - 1);
154                     r += (deltaA * deltaB * (n - 1)) / n;
155                     //Console.WriteLine("FF00::  " + ii + " --  " + meanA + " -- " + meanB + " -- " + varA + "  ---  " + varB);
156                 }
157 
158                 if (ieB.MoveNext())
159                 {
160                     //throw new ArgumentOutOfRangeException("dataA", Resources.ArgumentArraysSameLength);
161                 }
162             }
163             return (r / Math.Sqrt(varA * varB)) * (r / Math.Sqrt(varA * varB));
164         }
165         #endregion
166 
167         #region 最小二乘法部分
168 
169         #region 计算增广矩阵
170         static private double[] Cal_Guass(double[,] guass, int count)
171         {
172             double temp;
173             double[] x_value;
174 
175             for (int j = 0; j < count; j++)
176             {
177                 int k = j;
178                 double min = guass[j, j];
179 
180                 for (int i = j; i < count; i++)
181                 {
182                     if (Math.Abs(guass[i, j]) < min)
183                     {
184                         min = guass[i, j];
185                         k = i;
186                     }
187                 }
188 
189                 if (k != j)
190                 {
191                     for (int x = j; x <= count; x++)
192                     {
193                         temp = guass[k, x];
194                         guass[k, x] = guass[j, x];
195                         guass[j, x] = temp;
196                     }
197                 }
198 
199                 for (int m = j + 1; m < count; m++)
200                 {
201                     double div = guass[m, j] / guass[j, j];
202                     for (int n = j; n <= count; n++)
203                     {
204                         guass[m, n] = guass[m, n] - guass[j, n] * div;
205                     }
206                 }
207 
208                 /* System.Console.WriteLine("初等行变换：");
209                  for (int i = 0; i < count; i++)
210                  {
211                      for (int m = 0; m < count + 1; m++)
212                      {
213                          System.Console.Write("{0,10:F6}", guass[i, m]);
214                      }
215                      Console.WriteLine();
216                  }*/
217             }
218             x_value = Get_Value(guass, count);
219 
220             return x_value;
221 
222             /*if (x_value == null)
223                 Console.WriteLine("方程组无解或多解！");
224             else
225             {
226                 foreach (double x in x_value)
227                 {
228                     Console.WriteLine("{0:F6}", x);
229                 }
230             }*/
231         }
232 
233         #endregion
234 
235         #region 回带计算X值
236         static private double[] Get_Value(double[,] guass, int count)
237         {
238             double[] x = new double[count];
239             double[,] X_Array = new double[count, count];
240             int rank = guass.Rank;//秩是从0开始的
241 
242             for (int i = 0; i < count; i++)
243                 for (int j = 0; j < count; j++)
244                     X_Array[i, j] = guass[i, j];
245 
246             if (X_Array.Rank < guass.Rank)//表示无解
247             {
248                 return null;
249             }
250 
251             if (X_Array.Rank < count - 1)//表示有多解
252             {
253                 return null;
254             }
255             //回带计算x值
256             x[count - 1] = guass[count - 1, count] / guass[count - 1, count - 1];
257             for (int i = count - 2; i >= 0; i--)
258             {
259                 double temp = 0;
260                 for (int j = i; j < count; j++)
261                 {
262                     temp += x[j] * guass[i, j];
263                 }
264                 x[i] = (guass[i, count] - temp) / guass[i, i];
265             }
266 
267             return x;
268         }
269         #endregion
270 
271         #region  得到数据的法矩阵,输出为发矩阵的增广矩阵
272         static private double[,] Get_Array(double[] y, double[] x, int n)
273         {
274             double[,] result = new double[n + 1, n + 2];
275 
276             if (y.Length != x.Length)
277             {
278                 throw (new Exception("两个输入数组长度不一！"));
279                 //return null;
280             }
281 
282             for (int i = 0; i <= n; i++)
283             {
284                 for (int j = 0; j <= n; j++)
285                 {
286                     result[i, j] = Cal_sum(x, i + j);
287                 }
288                 result[i, n + 1] = Cal_multi(y, x, i);
289             }
290 
291             return result;
292         }
293 
294         #endregion
295 
296         #region 累加的计算
297         static private double Cal_sum(double[] input, int order)
298         {
299             double result = 0;
300             int length = input.Length;
301 
302             for (int i = 0; i < length; i++)
303             {
304                 result += Math.Pow(input[i], order);
305             }
306 
307             return result;
308         }
309         #endregion
310 
311         #region 计算∑(x^j)*y
312         static private double Cal_multi(double[] y, double[] x, int order)
313         {
314             double result = 0;
315 
316             int length = x.Length;
317 
318             for (int i = 0; i < length; i++)
319             {
320                 result += Math.Pow(x[i], order) * y[i];
321             }
322 
323             return result;
324         }
325         #endregion
326 
327         #endregion
328     }
329 }

View Code

posted @ 2020-06-17 09:37 萌丶丿主阅读(846) 评论(0) 收藏举报

刷新页面返回顶部

萌丶丿主

参数线性拟合类：包含（一次线性、二次线性、幂函数、E的指数、对数）

公告