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 导入
}
}
Excel txt导入导出到 LISTVIEW
最新推荐文章于 2022-06-14 23:08:24 发布
扫一扫
1437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
