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