导出Excel相关的代码
2015-12-11 08:53:36 访问(1599) 赞(0) 踩(0)
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 导出全部ToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
ExportFile(true);
}
catch (Exception err)
{
MsgForm.MsgException(err);
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 导出选中ToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
ExportFile(false);
}
catch (Exception err)
{
MsgForm.MsgException(err);
}
}
/// <summary>
///
/// </summary>
protected void ExportFile(bool isAll)
{
SaveFileDialog fd = new SaveFileDialog();
fd.Filter = "xls 文件(*.xls)|*.xls|csv 文件(*.csv)|*.csv";
fd.FileName = this.Text;
if (fd.ShowDialog() != DialogResult.OK)
return;
string fileName = fd.FileName;
ClientUtil cu = ClientUtil.instance;
IAppHelper ah = AppDriver.IAppHelperGet();
mainGridIndex mi = mainGridIndex.instance;
List<int> intArray = ah.IntArrayBuild
(
0,
listView_Main.Columns.Count - 1
);
List<List<string>> theList
=
null;
if (isAll)
{
theList
=
cu.ListViewToExcelList
(
listView_Main,
intArray
);
}
else
{
theList
=
cu.ListViewToExcelList8Selected
(
listView_Main,
listView_Main.SelectedItems,
intArray
);
}
eKing.GpsApp.Enums.ExportModel.EmExportModel em
=
ah.EmExportModelGet(fileName);
ah.ExportToExcel
(
em,
fileName,
theList
);
cu.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,
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>
///
/// </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="lv"></param>
/// <param name="isAllColumn"></param>
/// <param name="exportIndex"></param>
/// <returns></returns>
public List<List<string>> ListViewToExcelList
(
ListView lv,
bool isAllColumn,
List<int> exportIndex
)
{
List<List<string>> theResult = new List<List<string>>();
List<string> sonList = null;
sonList = new List<string>();
theResult.Add(sonList);
if (isAllColumn)
{
foreach (int i in exportIndex)
{
sonList.Add(lv.Columns[i].Text);
}
foreach (ListViewItem lvItem in lv.Items)
{
sonList = new List<string>();
theResult.Add(sonList);
foreach (int i in exportIndex)
{
sonList.Add
(
lvItem.SubItems[i].Text
);
}
}
}
else
{
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="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>
///
/// </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,
bool isAllColumn,
List<int> exportIndex
)
{
List<List<string>> theResult
=
new List<List<string>>();
List<string> sonList = null;
if (isAllColumn)
{
sonList = new List<string>();
theResult.Add(sonList);
foreach (int i in exportIndex)
{
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)
{
sonList.Add
(
lvItem.SubItems[i].Text
);
}
}
}
}
else
{
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>
/// 获得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 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>
/// 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()
+ "）未知，对应的代码尚未实现。"
);
}
}
/// <summary>
/// 保存成功后的提示操作
/// </summary>
/// <param name="dirName"></param>
public void MessageBoxShowFileNameOpenYesNo(string fileName)
{
DialogResult dg
=
MessageBox.Show("保存成功，是否打开文件？", "选择操作", MessageBoxButtons.YesNo);
if (dg == DialogResult.No)
return;
System.Diagnostics.Process.Start("explorer.exe", fileName);
}
标签：
导出Excel相关的代码 
上一条：
下一条：
相关评论
发表评论