实例介绍
【实例简介】
【实例截图】
【实例截图】
【核心代码】
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Reflection;
using Excel;
using System.Collections;
using System.Collections.Specialized;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
namespace LogOperator.Class
{
public delegate void ColorEventHandler(ListViewItem lvi);
/// <summary>
/// 该类提供日志系统导入导出的方法、方式
/// </summary>
public class IOHelper
{
#region 属性 初始化
/// <summary>
/// 连接字符串参数 Provider和 Extended Properties
/// </summary>
private static string xlspro;
private static string xlsex;
private static int rowCount;
private static int columnCount;
private static int i;
private static int j;
private static string filePath;
private static DataSet ds;
private static string strConn;
/// <summary>
/// 定义一个委托 用于为不同的数据加色
/// </summary>
public static event ColorEventHandler color;
public IOHelper()
{
}
#endregion 属性 初始化
#region 导出
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="listView"></param>
public static void ExportTo(ListView listView)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel2003(*.xls)|*.xls|Excel2007(*.xlsx)|*.xlsx|文本文件(*.txt)|*.txt|All Files(*.*)|*.*";
dialog.FileName = "SysLog";
if (dialog.ShowDialog() == DialogResult.OK)
{
if (dialog.FilterIndex==3)
{
ExportToTxt(dialog,listView);
return;
}
//Microsoft.Office.Interop.Excel.Application :Excel全部应用程序接口
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//缺少的 Object 通过反射产生一个函数的默认参数
object missing = System.Reflection.Missing.Value;
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
//Workbook类表示 Excel 应用程序中的单个工作簿
Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
//XlWBATemplate 指定创建的workbook的种类
Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = null;
//****** 抬头 *********************************************************************************
range = xlSheet.get_Range("A1", "I1");
range.Merge(Missing.Value); // 合并单元格
range.Columns.AutoFit(); // 设置列宽为自动适应
// 设置单元格左边框加粗
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
// 设置单元格右边框加粗
range.Borders[XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range.Value2 = "系统日志记录";
range.Font.Size = 18; // 设置字体大小
range.Font.ColorIndex = 5; // 设置字体颜色
//range.Interior.ColorIndex = 6; // 设置单元格背景色
range.RowHeight = 25; // 设置行高
range.ColumnWidth = 20; // 设置列宽
xlSheet.Cells[2, 1] = "记录时间";
xlSheet.Cells[2, 2] = "IP地址";
xlSheet.Cells[2, 3] = "进程";
xlSheet.Cells[2, 4] = "主机名";
xlSheet.Cells[2, 5] = "线程ID";
xlSheet.Cells[2, 6] = "级别";
xlSheet.Cells[2, 7] = "日志名称";
xlSheet.Cells[2, 8] = "消息";
xlSheet.Cells[2, 9] = "异常";
int rowIndex = 3;//这个用来标记数据有多少行位置
//-----------------------设置单元格 --------------------------------------------------------------------------------
range = xlSheet.get_Range(xlSheet.Cells[3, 9], xlSheet.Cells[rowIndex listView.Items.Count, 9]);//异常
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "@";//文本格式
range.ColumnWidth = 200;
range = xlSheet.get_Range(xlSheet.Cells[3, 8], xlSheet.Cells[rowIndex listView.Items.Count, 8]);//消息
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "@";//文本格式
range.ColumnWidth = 50;
range = xlSheet.get_Range(xlSheet.Cells[3, 7], xlSheet.Cells[rowIndex listView.Items.Count, 7]);//日志名称
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "@";//文本格式
range.ColumnWidth = 20;
range = xlSheet.get_Range(xlSheet.Cells[3, 6], xlSheet.Cells[rowIndex listView.Items.Count, 6]);//级别
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "@";//文本格式
range.ColumnWidth = 5;
range = xlSheet.get_Range(xlSheet.Cells[3, 5], xlSheet.Cells[rowIndex listView.Items.Count, 5]);//线程ID
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "@";//文本格式
range.ColumnWidth = 10;
range = xlSheet.get_Range(xlSheet.Cells[3, 4], xlSheet.Cells[rowIndex listView.Items.Count, 4]); //主机名
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.ColumnWidth = 18;
range = xlSheet.get_Range(xlSheet.Cells[3, 3], xlSheet.Cells[rowIndex listView.Items.Count, 3]); //进程
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.ColumnWidth = 20;
range = xlSheet.get_Range(xlSheet.Cells[3, 2], xlSheet.Cells[rowIndex listView.Items.Count, 2]); //IP
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "@";//文本格式
range.ColumnWidth = 13;
range = xlSheet.get_Range(xlSheet.Cells[3, 1], xlSheet.Cells[rowIndex listView.Items.Count, 1]); //时间
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal = "yyyy-MM-dd HH:mm";//日期格式
range.ColumnWidth = 20;
//-----------------------设置单元格 --------------------------------------------------------------------------------
//标题栏
range = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, 9]);
range.Interior.ColorIndex = 45;//设置标题背景色为 浅橙色
range.Font.Bold = true;//标题字体加粗
foreach (ListViewItem objItem in listView.Items)
{
xlSheet.Cells[rowIndex, 1] = Convert.ToDateTime(objItem.Text);
xlSheet.Cells[rowIndex, 2] = objItem.SubItems[1].Text;
xlSheet.Cells[rowIndex, 3] = objItem.SubItems[2].Text;
xlSheet.Cells[rowIndex, 4] = objItem.SubItems[3].Text;
xlSheet.Cells[rowIndex, 5] = objItem.SubItems[4].Text;
xlSheet.Cells[rowIndex, 6] = objItem.SubItems[5].Text;
xlSheet.Cells[rowIndex, 7] = objItem.SubItems[6].Text;
xlSheet.Cells[rowIndex, 8] = objItem.SubItems[7].Text;
xlSheet.Cells[rowIndex, 9] = objItem.SubItems[8].Text;
rowIndex = 1;
}
//数据区域
range = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowIndex, 9]);
range.Borders.LineStyle = 1;
range.Font.Size = 10;
range = xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, 9]);
range.Merge(Missing.Value); // 合并单元格
// range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
// 设置单元格右边框加粗
// range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;
range.RowHeight = 20;
range.Value2 = "导出时间: " DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
//***** 格式设定 ******************************************************************************
if (xlSheet != null)
{
xlSheet.SaveAs(dialog.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
xlApp.Visible = true;
}
}
catch (Exception)
{
xlApp.Quit();
//throw;
}
}
}
/// <summary>
///导出到txt
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private static void ExportToTxt(SaveFileDialog dl,ListView lst)
{
using (StreamWriter objWriter = new StreamWriter(dl.FileName, false, System.Text.Encoding.UTF8))
{
foreach (ListViewItem objItem in lst.Items)
{
objWriter.WriteLine(objItem.Text.PadRight(20) "@" objItem.SubItems[1].Text.PadLeft(15) "@" objItem.SubItems[2].Text.PadLeft(20) "@" objItem.SubItems[3].Text.PadLeft(18)
"@" objItem.SubItems[4].Text.PadLeft(10) "@" objItem.SubItems[5].Text.PadLeft(8) "@" objItem.SubItems[6].Text.PadLeft(20)
"@" objItem.SubItems[7].Text.PadLeft(15) "@" objItem.SubItems[8].Text.PadLeft(100));
}
objWriter.Close();
}
}
#endregion 导出
#region 导入
/// <summary>
/// 导入方法
/// </summary>
/// <param name="lst"></param>
public static void ImportMethod(ListView lst)
{
OpenFileDialog open = new OpenFileDialog();
if (open.ShowDialog() == DialogResult.OK)
{
filePath = open.FileName;
}
else
{
return;
}
string extName = System.IO.Path.GetExtension(filePath);
if (extName == ".txt")
{
ds = doImportTxt(filePath);
rowCount = ds.Tables[0].Rows.Count;
columnCount = ds.Tables[0].Columns.Count;
lst.BeginUpdate();
lst.Items.Clear();
string[] lstitems = new string[columnCount];
for (i = 0; i < rowCount; i )
{
for (j = 0; j < columnCount; j )
{
lstitems[j] = ds.Tables[0].Rows[i][j].ToString();
}
ListViewItem lvi = new ListViewItem(lstitems);
color(lvi);//为listview各项 添加背景色
lst.Items.Add(lvi);
}
lst.Refresh();
lst.EndUpdate();
}
else if (extName == ".xls" || extName == ".xlsx")
{
ds = doImportExcle(filePath);
rowCount = ds.Tables[0].Rows.Count;
columnCount = ds.Tables[0].Columns.Count;
lst.BeginUpdate();
lst.Items.Clear();
string[] lstitems = new string[columnCount];
for (i = 1; i < rowCount - 1; i )
{
for (j = 0; j < columnCount; j )
{
lstitems[j] = ds.Tables[0].Rows[i][j].ToString();
}
ListViewItem lvi = new ListViewItem(lstitems);
color(lvi);
lst.Items.Add(lvi);
}
lst.Refresh();
lst.EndUpdate();
}
else
{
MessageBox.Show("导入格式错误！");
}
}
#region 从excel导入
private static DataSet doImportExcle(string strFileName)
{
if (strFileName == "") return null;
string extName = System.IO.Path.GetExtension(strFileName);
if (extName == ".xls")
{
xlspro = ConfigurationManager.AppSettings["xls03pro"].ToString();
xlsex = ConfigurationManager.AppSettings["xls03ex"].ToString();
}
else
{
if (extName == ".xlsx")
{
xlspro = ConfigurationManager.AppSettings["xls07pro"].ToString();
xlsex = ConfigurationManager.AppSettings["xls07ex"].ToString();
}
else
{
return null;
}
}
string strConn = xlspro "Data Source=" strFileName ";" xlsex;
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs);
}
catch (Exception err)
{
System.Console.WriteLine(err.ToString());
}
return ExcelDs;
}
#endregion 从excel导入
#region 从txt导入
public static DataSet doImportTxt(string strFileName)
{
if (strFileName == "") return null;
string sLine = "";
DataSet dt;
StreamReader objReader = new StreamReader(strFileName, Encoding.GetEncoding("GB2312"));//实现一个 TextReader，使其以GB2312编码从字节流中读取字符。
sLine = objReader.ReadLine();//从当前流中读取一行字符并将数据作为字符串返回。
objReader.Close();
//string[] starry = sLine.ToString().Trim().Split('@');
string[] starry = sLine.ToString().Split('@');
dt = TextFileLoader(strFileName, "TableName", starry);
// DataSet dt = TextFileLoader(strFileName, "TableName");
return dt;
}
static int x = 0;
public static DataSet TextFileLoader(string FilePath, string TableName, string[] FieldsInArray) //, string[] FieldsInArray
{
DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable(TableName);//用指定的表明初始化datatable
FileStream fs = File.Open(FilePath, FileMode.Open, FileAccess.Read);//使用指定的路径、创建模式和读/写权限初始化 FileStream 类的新实例。
StreamReader sr = new StreamReader(fs, Encoding.GetEncoding("GB2312"));
for (int i = 0; i < FieldsInArray.Length; i )
{
//dt.Columns.Add(new DataColumn(FieldsInArray[i].Trim().Substring(0, (FieldsInArray[i].Trim().Length - 1)), typeof(string)));
dt.Columns.Add(new DataColumn(FieldsInArray[i].Trim().Substring(0, (FieldsInArray[i].Trim().Length)), typeof(string)));
}
string strRead;
bool flag = true;
while (flag)
{
strRead = sr.ReadLine();
if (!string.IsNullOrEmpty(strRead))
{
string[] aryVale = strRead.Split('@');
DataRow dr = dt.NewRow();
for (int k = 0; k < aryVale.Length; k )
{
if (!string.IsNullOrEmpty(aryVale[k].Trim()))
{
string st = aryVale[k].Trim().Substring(0, (aryVale[k].Trim().Length));
dr[k] = st;
}
else {
dr[k] = aryVale[k].Trim();
}
}
dt.Rows.Add(dr);
}
else
{
flag = false;
}
x ;
}
ds.Tables.Add(dt);
return ds;
}
#endregion 从txt导入
#endregion 导入
}
}
好例子网口号：伸出你的我的手 — 分享！
小贴士
感谢您为本站写下的评论，您的评论对其它用户来说具有重要的参考价值，所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字，对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙，所以请不要反馈意义不大的重复字符，也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明，或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦，又没人会搭理，于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享，对搜索内容的合法性不具有预见性、识别性、控制性，仅供学习研究，请务必在下载后24小时内给予删除，不得用于其他任何用途，否则后果自负。基于互联网的特殊性，平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查；无论平台是否已进行审查，用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场，基于网友分享，根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定，若资源存在侵权或相关问题请联系本站客服人员，点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论