📄 commonexport.cs

📁 C#自定义查询控件
💻 CS
📖 第 1 页 / 共 2 页
字号:
上一页 12
			StreamWriter sw = new StreamWriter(file_name,true,System.Text.Encoding.Default);
			try
			{
				if (showProgress)
					ProgressForm.CreateProgress("正在输出到文件“" + file_name + "”！", table.Rows.Count);
                             
				//输出字段名称
				strLine = "";
				for(int i = 0; i < outFields.Count; i++)
					strLine += outFields[i].Field_Name + separator;
				strLine = strLine.Remove(strLine.Length - separator.Length,separator.Length);
				sw.WriteLine(strLine);

				//输出记录
				for(int j = 0; j < table.Rows.Count; j++)
				{
					drow = table.Rows[j];
					strLine = "";
					for(int i = 0; i < outFields.Count; i++)
					{
						if (drow.IsNull(outFields[i].Field_Index))
							strLine += "null" + separator;
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.String"))
							strLine += drow[outFields[i].Field_Index].ToString().Replace("\n","").Replace("\r","").Replace(separator,"") + separator;
						else 
							strLine += drow[outFields[i].Field_Index].ToString() + separator;
					}
					
					strLine = strLine.Remove(strLine.Length - separator.Length,separator.Length);
					sw.WriteLine(strLine);

					if (showProgress)
						ProgressForm.UpdateProgress(j+1);
				}
			}
			catch(Exception e)
			{
				MessageBox.Show(e.Message);
				return false;
			}
			finally
			{
				if (showProgress)
					ProgressForm.DeleteProgress();
				sw.Close();
			}
			OpenFile(file_name);
			return true;
		}
		public bool ExportToHtml(string title)
		{
			string file_name;
			string strLine;
			DataRow drow;

			if (! ReadyToExport())
				return false;

			file_name = GetFileName(".htm");

			if (file_name == "")
				return false;

			if (! GetFieldList())
				return false;

			if (title == "")
				title = "导出数据";

			//找开文件
			
			StreamWriter sw = new StreamWriter(file_name,true,System.Text.Encoding.Default);

			try
			{
				if (showProgress)
					ProgressForm.CreateProgress("正在输出到文件“" + file_name + "”！", table.Rows.Count);
                
				//写文件头
				sw.WriteLine("<html>");
				sw.WriteLine("<head><title>" + title + "</title></head>");
				sw.WriteLine("<body>");
				sw.WriteLine("<Div align=center Style=\"font-size:12pt\">" + title + "</Div>");
				sw.WriteLine("<table Style=\"font-size:9pt\" width=100% border=1 align=center cellpadding=0 cellspacing=1 bordercolor=#9ab774>");        
				//输出字段名称
				sw.WriteLine("<tr bgcolor=#DEE7CE>");
				strLine = "";
				for(int i = 0; i < outFields.Count; i++)
					strLine += "<td>" + outFields[i].Field_Name + "</td>";
				sw.WriteLine(strLine);
				sw.WriteLine("</tr>");

				//输出记录
				for(int j = 0; j < table.Rows.Count; j++)
				{
					drow = table.Rows[j];
					sw.WriteLine("<tr>");
					strLine = "";
					for(int i = 0; i < outFields.Count; i++)
					{
						if (drow.IsNull(outFields[i].Field_Index))
							strLine += "<td>" + "null" + "</td>";
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.String"))
							strLine += "<td>" + drow[outFields[i].Field_Index].ToString().Replace("\n","").Replace("\r","") + "</td>";
						else 
							strLine += "<td>" + drow[outFields[i].Field_Index].ToString() + "</td>";
					}
					sw.WriteLine(strLine);
					sw.WriteLine("</tr>");

					if (showProgress)
						ProgressForm.UpdateProgress(j+1);
				}
				//写文件尾
				sw.WriteLine("</table>");
				sw.WriteLine("</body>");
				sw.WriteLine("</html>");
			}
			catch(Exception e)
			{
				MessageBox.Show(e.Message);
				return false;
			}
			finally
			{
				if (showProgress)
					ProgressForm.DeleteProgress();
				sw.Close();
			}
			OpenFile(file_name);
			return true;
		}
		public bool ExportToHtml()
		{
			return ExportToHtml("");
		}
		public bool ExportToCSV()
		{
			return ExportToText(",",".csv");
		}
		public bool ExportToText()
		{
			return ExportToText("\t",".txt");
		}
		public bool ExportToText(string separator)
		{
			return ExportToText(separator,".txt");
		}
		public bool ExportToExcel(string sheetName)
		{
			string conStr;
			string file_name;
			string fields;
			string sqlStr;
			int columnLength;
			DataRow drow;

			if (! ReadyToExport())
				return false;

			file_name = GetFileName(".xls");

			if (file_name == "")
				return false;

			if (! GetFieldList())
				return false;

			if (sheetName == "")
				sheetName = table.TableName;

			//生成数据源连接字串
			conStr = string.Format("DRIVER={{Microsoft Excel Driver (*.xls)}};DSN=;FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB={0};DBQ={0}",file_name);

			OdbcConnection excelCn = new OdbcConnection(conStr); 
			OdbcCommand excelCmd = new OdbcCommand();
			try
			{
				excelCn.Open();
				excelCmd.Connection = excelCn;

				if (showProgress)
					ProgressForm.CreateProgress("正在输出到文件“" + file_name + "”！", table.Rows.Count);
                             
				fields = "";
				sqlStr = "";

				for(int i = 0; i < outFields.Count; i++)
				{
					sqlStr += "[" + outFields[i].Field_Name + "] ";
					///
					if ((table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Int32"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.UInt32"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Int16"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.UInt16")))
						sqlStr += "int,";
					else if ((table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Double"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Single"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Decimal")))
						sqlStr += "numeric,";
					else if (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.DateTime"))
						sqlStr += "datetime,";
					else if (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Boolean"))
						sqlStr += "boolean,";
					else if ((table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Int64"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.UInt64")))
						sqlStr += "long,";
					else
					{
						columnLength = GetColumnLength(outFields[i].Field_Index);
						if (columnLength > 255)
							sqlStr += "memo,";
						else
							sqlStr += "varchar(" + columnLength.ToString() + "),";

					}
					fields += "[" + outFields[i].Field_Name + "],";
				}

				sqlStr = sqlStr.Remove(sqlStr.Length - 1,1);
				fields = fields.Remove(fields.Length - 1,1);

				//添加字段名
				sqlStr = "CREATE TABLE [" + sheetName + "] (" + sqlStr + ")";
				excelCmd.CommandText = sqlStr;
				excelCmd.ExecuteNonQuery();

				//插入记录
				for(int j = 0; j < table.Rows.Count; j++)
				{
					drow = table.Rows[j];
					sqlStr = "";
					for(int i = 0; i < outFields.Count; i++)
					{
						if (drow.IsNull(outFields[i].Field_Index))
							sqlStr += "null,";
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.String"))
							sqlStr += "'" + drow[outFields[i].Field_Index].ToString().Replace("\n","").Replace("\r","").Replace("'","") + "',";
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.DateTime"))
							sqlStr += "'" + drow[outFields[i].Field_Index].ToString() + "',";
						else 
							sqlStr += drow[outFields[i].Field_Index].ToString() + ",";
					}
					sqlStr = sqlStr.Remove(sqlStr.Length - 1,1);

					sqlStr = "INSERT INTO [" + sheetName + "](" + fields + ") VALUES(" + sqlStr + ")";

					excelCmd.CommandText = sqlStr;
					excelCmd.ExecuteNonQuery();
					if (showProgress)
					ProgressForm.UpdateProgress(j+1);
				}
			}
			catch(Exception e)
			{
				MessageBox.Show(e.Message);
				return false;
			}
			finally
			{
				if (showProgress)
					ProgressForm.DeleteProgress();
				excelCn.Close();
				excelCmd.Dispose();
				excelCn.Dispose();
			}
			OpenFile(file_name);
			return true;
		}

		public bool ExportToExcel()
		{
			return ExportToExcel("");
		}
		public bool ExportToDBF()
		{
			string conStr;
			string file_name;
			string fields;
			string sqlStr;
			string tableName;
			int columnLength;
			DataRow drow;

			if (! ReadyToExport())
				return false;

			file_name = GetFileName(".dbf");

			if (file_name == "")
				return false;

			if (! GetFieldList())
				return false;

			//生成数据源连接字串
			conStr = string.Format("DRIVER={{Microsoft dBase Driver (*.dbf)}};FIL=dBase 5.0;DSN=;FIRSTROWHASNAMES=1;READONLY=FALSE;DEFAULTDIR={0}",Path.GetDirectoryName(file_name));

			OdbcConnection dbfCn = new OdbcConnection(conStr); 
			OdbcCommand dbfCmd = new OdbcCommand();
			try
			{
				dbfCn.Open();
				dbfCmd.Connection = dbfCn;

				if (showProgress)
					ProgressForm.CreateProgress("正在输出到文件“" + file_name + "”！", table.Rows.Count);
                             
				fields = "";
				sqlStr = "";

				for(int i = 0; i < outFields.Count; i++)
				{
					sqlStr += "[" + outFields[i].Field_Name + "] ";
					///
					if ((table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Int32"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.UInt32"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Int16"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.UInt16")))
						sqlStr += "int,";
					else if ((table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Double"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Single"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Decimal")))
						sqlStr += "numeric,";
					else if (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.DateTime"))
						sqlStr += "datetime,";
					else if (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Boolean"))
						sqlStr += "boolean,";
					else if ((table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.Int64"))
						|| (table.Columns[outFields[i].Field_Index].DataType == System.Type.GetType("System.UInt64")))
						sqlStr += "long,";
					else
					{
						columnLength = GetColumnLength(outFields[i].Field_Index);
						if (columnLength > 254)
							sqlStr += "memo,";
						else
							sqlStr += "varchar("+ columnLength.ToString() +"),";
					}
					fields += "[" + outFields[i].Field_Name + "],";
				}

				sqlStr = sqlStr.Remove(sqlStr.Length - 1,1);
				fields = fields.Remove(fields.Length - 1,1);

				//添加字段名
				tableName = Path.GetFileNameWithoutExtension(file_name);
				sqlStr = "CREATE TABLE [" + tableName + "] (" + sqlStr + ")";
				dbfCmd.CommandText = sqlStr;
				dbfCmd.ExecuteNonQuery();

				//插入记录
				for(int j = 0; j < table.Rows.Count; j++)
				{
					drow = table.Rows[j];
					sqlStr = "";
					for(int i = 0; i < outFields.Count; i++)
					{
						if (drow.IsNull(outFields[i].Field_Index))
							sqlStr += "null,";
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.String"))
							sqlStr += "'" + drow[outFields[i].Field_Index].ToString().Replace("\n","").Replace("\r","").Replace("'","") + "',";
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.DateTime"))
							sqlStr += "'" + drow[outFields[i].Field_Index].ToString() + "',";
						else 
							sqlStr += drow[outFields[i].Field_Index].ToString() + ",";
					}
					sqlStr = sqlStr.Remove(sqlStr.Length - 1,1);

					sqlStr = "INSERT INTO [" + tableName + "](" + fields + ") VALUES(" + sqlStr + ")";

					dbfCmd.CommandText = sqlStr;
					dbfCmd.ExecuteNonQuery();
					if (showProgress)
						ProgressForm.UpdateProgress(j+1);
				}
			}
			catch(Exception e)
			{
				MessageBox.Show(e.Message);
				return false;
			}
			finally
			{
				if (showProgress)
					ProgressForm.DeleteProgress();
				dbfCn.Close();
				dbfCmd.Dispose();
				dbfCn.Dispose();
			}
			return true;
		}
		public bool ExportToSDF()
		{
			string file_name;
			string strLine;
			string sValue;
			DataRow drow;

			if (! ReadyToExport())
				return false;

			file_name = GetFileName(".txt");

			if (file_name == "")
				return false;

			if (! GetFieldList())
				return false;


			//找开文件
			StreamWriter sw = new StreamWriter(file_name,true,System.Text.Encoding.Default);
			try
			{
				if (showProgress)
					ProgressForm.CreateProgress("正在输出到文件“" + file_name + "”！", table.Rows.Count);

				//计算字段长度
				for(int i = 0; i < outFields.Count; i++)
				{
					int l1;
					int l2;
					l1 = GetColumnLength(outFields[i].Field_Index);
					l2 = System.Text.Encoding.Default.GetByteCount(outFields[i].Field_Name);
					if (l1 < l2)
						l1 = l2;
					if (l1 < 4)
						l1 = 4;
					outFields[i].Field_Length = l1 + 1;
				}

                             
				
				//输出字段名称
				strLine = "";
				for(int i = 0; i < outFields.Count; i++)
					strLine += outFields[i].Field_Name + new string(' ',outFields[i].Field_Length - System.Text.Encoding.Default.GetByteCount(outFields[i].Field_Name));
				sw.WriteLine(strLine);

				//输出记录
				for(int j = 0; j < table.Rows.Count; j++)
				{
					drow = table.Rows[j];
					strLine = "";
					for(int i = 0; i < outFields.Count; i++)
					{
						if (drow.IsNull(outFields[i].Field_Index))
							sValue = "null";
						else if (table.Columns[outFields[i].Field_Index].DataType == Type.GetType("System.String"))
							sValue = drow[outFields[i].Field_Index].ToString().Replace("\n","").Replace("\r","");
						else 
							sValue = drow[outFields[i].Field_Index].ToString();
						strLine += sValue + new String(' ',outFields[i].Field_Length - System.Text.Encoding.Default.GetByteCount(sValue));
					}
					sw.WriteLine(strLine);

					if (showProgress)
						ProgressForm.UpdateProgress(j+1);
				}
			}
			catch(Exception e)
			{
				MessageBox.Show(e.Message);
				return false;
			}
			finally
			{
				if (showProgress)
					ProgressForm.DeleteProgress();
				sw.Close();
			}
			OpenFile(file_name);
			return true;
		}

		public bool Export()
		{
			string eType = "";
			string separator = "";
			if (! ExportForm.SelectType(ref eType,ref separator))
				return false;
			autoGetFileName = true;
			switch (eType)
			{
				case ".xls":
					ExportToExcel();
					break;
				case ".dbf":
					ExportToDBF();
					break;
				case ".htm":
					ExportToHtml();
					break;
				case ".csv":
					ExportToCSV();
					break;
				case ".txt":
					ExportToText(separator);
					break;
				case ".sdf":
					ExportToSDF();
					break;
				default:
					return false;
			}
			return true;
		}
	}
}
上一页 12
💿 文件大小 153 K
👤 上传用户 awake2
📂 所属分类其他
🏷️ 相关标签

#自定义 #查询 #控件
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -