导出Excel的相关代码
2016-01-27 09:41:56 访问(2584) 赞(0) 踩(0)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace eKing.GpsApp.BLLClasses
{
class Class1
{
/// <summary>
/// 导出全部和导出选中的记录
/// </summary>
/// <param name="lv"></param>
/// <param name="FileName"></param>
/// <param name="isAll"></param>
protected void ExportFile(ListView lv, string FileName, bool isAll)
{
SaveFileDialog fd = new SaveFileDialog();
fd.Filter = "xls 文件(*.xls)|*.xls|csv 文件(*.csv)|*.csv";
fd.FileName = FileName;
if (fd.ShowDialog() != DialogResult.OK)
return;
string fileName = fd.FileName;
List<int> intArray = IntArrayBuild
(
0,
lv.Columns.Count - 1
);
List<List<string>> theList
=
null;
if (isAll)
{
theList
=
ListViewToExcelList
(
lv,
intArray
);
}
else
{
theList
=
ListViewToExcelList8Selected
(
lv,
lv.SelectedItems,
intArray
);
}
eKing.GpsApp.Enums.ExportModel.EmExportModel em
=
EmExportModelGet(fileName);
ExportToExcel
(
em,
fileName,
theList
);
MessageBoxShowFileNameOpenYesNo(fileName);
}
/// <summary>
/// 构建List-int的索引
/// </summary>
/// <param name="fromIndex"></param>
/// <param name="toIndex"></param>
/// <param name="p"></param>
/// <returns></returns>
public List<int> IntArrayBuild
(
int fromIndex,
int toIndex
)
{
List<int> theResult = new List<int>();
int end = toIndex + 1;
for (int i = fromIndex; i < end; ++i)
{
theResult.Add(i);
}
return theResult;
}
/// <summary>
/// 构建List-int的索引
/// </summary>
/// <param name="fromIndex"></param>
/// <param name="toIndex"></param>
/// <param name="p"></param>
/// <returns></returns>
public List<int> IntArrayBuild8Params
(
int fromIndex,
int toIndex,
params int[] p
)
{
List<int> theResult = new List<int>();
int end = toIndex + 1;
if (p == null || p.Length == 0)
{
for (int i = fromIndex; i < end; ++i)
{
theResult.Add(i);
}
}
else
{
bool isNoFind = true;
for (int i = fromIndex; i < end; ++i)
{
isNoFind = true;
foreach (int j in p)
{
if (i == j)
{
isNoFind = false;
break;
}
}
if (isNoFind)
theResult.Add(i);
}
}
return theResult;
}
/// <summary>
/// 获得excel的文件类型
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public eKing.GpsApp.Enums.ExportModel.EmExportModel
EmExportModelGet(string fileName)
{
if (fileName == null || fileName.Length == 0)
return eKing.GpsApp.Enums.ExportModel.EmExportModel.csv;
fileName = fileName.Trim().ToLower();
if (fileName.EndsWith(".csv"))
return eKing.GpsApp.Enums.ExportModel.EmExportModel.csv;
return eKing.GpsApp.Enums.ExportModel.EmExportModel.xls;
}
#region Excel导出相关代码
#region CSV的导出
/// <summary>
/// 最大数字
/// </summary>
private const int CSV_MAX_LEN = 11;
/// <summary>
/// 是否是数字
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
protected bool CSVCellIsNumber(string str)
{
if (str == null)
return false;
int iLen = str.Length;
if (iLen == 0)
return false;
int startIndex = 0;
if (str[0] == '-')
startIndex = 1;
if (startIndex == iLen)
return false;
bool containsPoint = false;
for (int i = startIndex; i < iLen; ++i)
{
if (str[i] >= '0' && str[i] <= '9')
continue;
if (str[i] == '.')
{
if (containsPoint)
return false;
containsPoint = true;
continue;
}
return false;
}
return true;
}
/// <summary>
/// 是否转换
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
protected bool CSVCellNumberIsConvert(string str)
{
if (str == null)
return false;
int iLen = str.Length;
if (iLen == 0)
return false;
int startIndex = 0;
if (str[0] == '-')
startIndex = 1;
if (startIndex == iLen)
return false;
int idx = str.IndexOf('.');
string subStr = "";
if (idx == -1)
{
if (startIndex == 0)
subStr = str;
else
subStr = str.Substring(1);
}
else
{
subStr = str.Substring(startIndex, idx);
}
if (subStr.Length <= CSV_MAX_LEN)
return false;
else
return true;
}
/// <summary>
/// CSV转义
/// </summary>
/// <param name="text"></param>
/// <returns></returns>
protected string CSVCellConvert(string text)
{
if (text == null || text.Length == 0)
return "";
bool isNumberValue = CSVCellIsNumber(text);
if (isNumberValue)
{
bool isConvert = CSVCellNumberIsConvert(text);
if (isConvert)
return "\"" + '\t' + text + "\"";
else
return text;
}
if (text.Contains("\""))
{
text = text.Replace("\"", "\"\"");
return "\"" + text + "\"";
}
if (text.Contains(","))
{
return "\"" + text + "\"";
}
return text;
}
/// <summary>
/// 导出Excel模式
/// </summary>
/// <param name="fileName"></param>
/// <param name="theList"></param>
public void CSVExportTo
(
string fileName,
List<List<string>> theList
)
{
if (theList == null || theList.Count == 0)
return;
StringBuilder theResult = new StringBuilder();
int iCount = 0;
foreach (List<string> sonList in theList)
{
if (sonList == null)
continue;
iCount = sonList.Count;
if (iCount == 0)
continue;
theResult.Append(CSVCellConvert(sonList[0]));
for (int i = 1; i < iCount; ++i)
{
theResult.Append(",");
theResult.Append(CSVCellConvert(sonList[i]));
}
theResult.AppendLine();
}
WriteFile
(
fileName,
theResult.ToString(),
Encoding.GetEncoding("gb2312"),
true,
false
);
}
#endregion CSV的导出
/// <summary>
/// txt模式的导出
/// </summary>
/// <param name="fileName"></param>
/// <param name="fileText"></param>
public void TxtExportTo(string fileName, string fileText)
{
WriteFile
(
fileName,
fileText,
Encoding.GetEncoding("gb2312"),
true,
false
);
}
/// <summary>
/// NPOI模式的导出
/// </summary>
/// <param name="fileName"></param>
/// <param name="theList"></param>
public void NPOIExportTo
(
string fileName,
List<List<string>> theList
)
{
if (theList == null || theList.Count == 0)
return;
// 建一个内存块 //
System.IO.MemoryStream ms = null;
HSSFWorkbook book = null;
try
{
book = new HSSFWorkbook();
ISheet sheet = null;
sheet = book.CreateSheet("Sheet1");
IRow row = null;
ICell icellV = null;
string sValue = string.Empty;
int iCount = 0;
int rowIndex = -1;
foreach (List<string> sonList in theList)
{
if (sonList == null)
continue;
iCount = sonList.Count;
if (iCount == 0)
continue;
++rowIndex;
row = sheet.CreateRow(rowIndex);
for (int j = 0; j < iCount; ++j)
{
sValue = sonList[j];
if (sValue == null)
sValue = "";
icellV = row.CreateCell(j);
icellV.SetCellValue(sValue);
}
}
// 写入
ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Flush();
MemoryStreamToFile(ms, fileName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (ms != null)
{
ms.Close();
ms.Dispose();
ms = null;
}
book = null;
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="em"></param>
/// <param name="fileName"></param>
/// <param name="theList"></param>
public void ExportToExcel
(
eKing.GpsApp.Enums.ExportModel.EmExportModel em,
string fileName,
List<List<string>> theList
)
{
switch (em)
{
case eKing.GpsApp.Enums.ExportModel.EmExportModel.csv:
CSVExportTo(fileName, theList);
break;
case eKing.GpsApp.Enums.ExportModel.EmExportModel.xls:
NPOIExportTo(fileName, theList);
break;
default:
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "枚举（"
+ em.GetType().FullName
+ "."
+ em.ToString()
+ "）未知，对应的代码尚未实现。"
);
}
}
#endregion Excel导出相关代码
/// <summary>
/// 拼装选中的ListView行项数据到内存中
/// </summary>
/// <param name="lv"></param>
/// <param name="lvc"></param>
/// <param name="exportIndex"></param>
/// <returns></returns>
public List<List<string>> ListViewToExcelList8Selected
(
ListView lv,
ListView.SelectedListViewItemCollection lvc,
List<int> exportIndex
)
{
List<List<string>> theResult = new List<List<string>>();
List<string> sonList = null;
sonList = new List<string>();
theResult.Add(sonList);
foreach (int i in exportIndex)
{
if (lv.Columns[i].Width == 0)
continue;
sonList.Add(lv.Columns[i].Text);
}
if (lvc != null)
{
foreach (ListViewItem lvItem in lvc)
{
sonList = new List<string>();
theResult.Add(sonList);
foreach (int i in exportIndex)
{
if (lv.Columns[i].Width == 0)
continue;
sonList.Add
(
lvItem.SubItems[i].Text
);
}
}
}
return theResult;
}
/// <summary>
/// 拼装ListView的数据到内存中
/// </summary>
/// <param name="lv"></param>
/// <param name="exportIndex"></param>
/// <returns></returns>
public List<List<string>> ListViewToExcelList
(
ListView lv,
List<int> exportIndex
)
{
List<List<string>> theResult = new List<List<string>>();
List<string> sonList = null;
sonList = new List<string>();
theResult.Add(sonList);
foreach (int i in exportIndex)
{
if (lv.Columns[i].Width == 0)
continue;
sonList.Add(lv.Columns[i].Text);
}
foreach (ListViewItem lvItem in lv.Items)
{
sonList = new List<string>();
theResult.Add(sonList);
foreach (int i in exportIndex)
{
if (lv.Columns[i].Width == 0)
continue;
sonList.Add
(
lvItem.SubItems[i].Text
);
}
}
return theResult;
}
/// <summary>
/// 保存成功后的提示操作
/// </summary>
/// <param name="fileName"></param>
public void MessageBoxShowFileNameOpenYesNo(string fileName)
{
DialogResult dg
=
MessageBox.Show("保存成功，是否打开文件？", "选择操作", MessageBoxButtons.YesNo);
if (dg == DialogResult.No)
return;
System.Diagnostics.Process.Start("explorer.exe", fileName);
}
/// <summary>
/// MemoryStream内容写入文件
/// </summary>
/// <param name="ms"></param>
/// <param name="FileName"></param>
public void MemoryStreamToFile
(
MemoryStream ms,
string FileName
)
{
FileInfo fi
=
new FileInfo(FileName);
if (fi.Exists)
{
fi.Delete();
}
else
{
if (!fi.Directory.Exists)
fi.Directory.Create();
}
FileStream fs
=
new FileStream
(
fi.FullName,
FileMode.Create,
FileAccess.Write
);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
data = null;
ms = null;
fs = null;
}
#region 写入文件
/// <summary>
/// 写入文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="textValue">文件内容</param>
public void WriteFile
(
string fileName,
string textValue
)
{
WriteFile(fileName, textValue, null, true, true);
}
/// <summary>
/// 写入文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="textValue">文件内容</param>
/// <param name="textEncoding">编码</param>
public void WriteFile
(
string fileName,
string textValue,
System.Text.Encoding textEncoding
)
{
WriteFile(fileName, textValue, textEncoding, true, true);
}
/// <summary>
/// 写入文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="textValue">文件内容</param>
/// <param name="textEncoding">编码</param>
/// <param name="IsOverwrite">是否覆盖</param>
public void WriteFile
(
string fileName,
string textValue,
System.Text.Encoding textEncoding,
bool IsOverwrite
)
{
WriteFile(fileName, textValue, textEncoding, IsOverwrite, true);
}
/// <summary>
/// 写入文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="textValue">文件内容</param>
/// <param name="textEncoding">编码</param>
/// <param name="IsOverwrite">是否覆盖</param>
/// <param name="IsAppend">是否追加</param>
public void WriteFile
(
string fileName,
string textValue,
System.Text.Encoding textEncoding,
bool IsOverwrite,
bool IsAppend
)
{
if (fileName == null || fileName.Length == 0)
{
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "传入参数："
+ "string fileName"
+ "为null或为空。"
);
}
FileInfo info = new FileInfo(fileName);
if (info.Exists)
{
// 文件存在，且不覆盖 //
if (!IsOverwrite)
{
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "文件" + info.FullName + "已存在，并且不允许覆盖。"
);
}
// 如果不是追加 //
if (!IsAppend)
{
File.Delete(info.FullName);
}
}
else
{
// 创建目录 //
if (!info.Directory.Exists)
{
Directory.CreateDirectory(info.Directory.FullName);
}
}
FileStream fs = null;
StreamWriter m_streamWriter = null;
try
{
//创建一个文件流，用以写入或者创建一个StreamWriter
fs = new FileStream(info.FullName, FileMode.OpenOrCreate, FileAccess.Write);
if (textEncoding == null)
{
m_streamWriter = new StreamWriter(fs);
}
else
{
m_streamWriter = new StreamWriter(fs, textEncoding);
}
m_streamWriter.Flush();
// 使用StreamWriter来往文件中写入内容
if (IsAppend)
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.Write(textValue);
m_streamWriter.Flush();
}
catch (Exception err)
{
throw err;
}
finally
{
if (m_streamWriter != null)
{
m_streamWriter.Flush();
m_streamWriter.Close();
}
if (fs != null)
{
fs.Close();
}
m_streamWriter = null;
fs = null;
}
}
#endregion 写入文件
}
}
标签：
导出Excel的相关代码 
上一条：
下一条：
相关评论
发表评论