|
|
using System;
|
|
|
using System.Collections;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Data;
|
|
|
using System.Data.Odbc;
|
|
|
using System.Data.OleDb;
|
|
|
using System.Diagnostics;
|
|
|
using System.IO;
|
|
|
using System.Reflection;
|
|
|
using System.Text;
|
|
|
using NetLibrary.Data;
|
|
|
using NPOI.HSSF.UserModel;
|
|
|
using NPOI.HSSF.Util;
|
|
|
using NPOI.SS.UserModel;
|
|
|
using NPOI.SS.Util;
|
|
|
using NetLibrary.ReportPrint;
|
|
|
|
|
|
|
|
|
namespace NetLibrary
|
|
|
{
|
|
|
public class MicrosoftExcel
|
|
|
{
|
|
|
#region 导出
|
|
|
|
|
|
|
|
|
public string ExportTXT(string Content)
|
|
|
{
|
|
|
|
|
|
|
|
|
try
|
|
|
{
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (Directory.Exists(DirectoryName) == false) Directory.CreateDirectory(DirectoryName);
|
|
|
|
|
|
string filename = Guid.NewGuid().ToString() + ".txt";
|
|
|
string filePath = DirectoryName + "/" + filename;
|
|
|
File.Create(filePath).Close();
|
|
|
FileInfo f = new FileInfo(filePath);
|
|
|
|
|
|
using (StreamWriter sw = new StreamWriter(filePath, false, System.Text.Encoding.UTF8))
|
|
|
{
|
|
|
|
|
|
sw.Write(Content);
|
|
|
|
|
|
sw.Close();
|
|
|
}
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
catch
|
|
|
{
|
|
|
return "";
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 导出Excel
|
|
|
/// </summary>
|
|
|
/// <param name="HeaderName">表头</param>
|
|
|
/// <param name="tb">DataTable</param>
|
|
|
/// <param name="Columns">要导出的列集合</param>
|
|
|
/// <returns></returns>
|
|
|
public string Export(string HeaderName, DataTable tb)
|
|
|
{
|
|
|
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
|
|
|
HSSFRow dataRow = null;
|
|
|
HSSFCell dataCell = null;
|
|
|
int RowIndex = 0;
|
|
|
if (HeaderName != "")
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(0);
|
|
|
dataCell.SetCellValue(HeaderName);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
sheet.AddMergedRegion(new Region(0, 0, 0, tb.Columns.Count - 1));
|
|
|
RowIndex++;
|
|
|
}
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + RowIndex);
|
|
|
foreach (DataColumn item in tb.Columns)
|
|
|
{
|
|
|
int j = tb.Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.ColumnName].ToString());
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
for (int i = 0; i < tb.Columns.Count; i++)
|
|
|
{
|
|
|
sheet.AutoSizeColumn(i);
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
|
|
|
public string Export(DataTable tb, ReportPrint.TableColumnCollection Columns)
|
|
|
{
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
//填充表头
|
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
//dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
//dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
//dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
//dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
////边框颜色
|
|
|
//dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
//dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
//dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
//dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (item.DataType == DbType.Decimal && tb.Rows[i][item.Name] != null && tb.Rows[i][item.Name].ToString() != "")
|
|
|
dataCell.SetCellValue(Convert.ToDouble(tb.Rows[i][item.Name]));
|
|
|
else if (item.DataType != DbType.Decimal)
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
//dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
//dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
//dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
//dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
////边框颜色
|
|
|
//dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
//dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
//dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
//dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
|
|
|
|
|
|
public string ExportTwo(DataTable tb, DataTable tb1, ReportPrint.TableColumnCollection Columns, ReportPrint.TableColumnCollection Columns1)
|
|
|
{
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
//填充表头
|
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
|
|
|
HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("Sheet2");
|
|
|
sheet1.PrintSetup.FitHeight = 0;
|
|
|
sheet1.PrintSetup.FitWidth = 0;
|
|
|
sheet1.PrintSetup.Scale = 100;
|
|
|
//填充表头
|
|
|
HSSFRow dataRow1 = (HSSFRow)sheet1.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns1)
|
|
|
{
|
|
|
int index = Columns1.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow1.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb1.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow1 = (HSSFRow)sheet1.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns1)
|
|
|
{
|
|
|
int j = Columns1.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow1.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb1.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(tb1.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns1)
|
|
|
{
|
|
|
int index = Columns1.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet1.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet1.AutoSizeColumn(index); }
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 2013-12-27
|
|
|
/// 石志超
|
|
|
/// </summary>
|
|
|
public string Export<T>(List<T> lst, ReportPrint.TableColumnCollection Columns) where T : class
|
|
|
{
|
|
|
Type t = typeof(T);
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
//sheet.PrintSetup.PaperSize = 9;
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
//填充表头
|
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < lst.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(
|
|
|
DataConvert.Format(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)), item.Format));
|
|
|
}
|
|
|
else if (item.DataType == DbType.Int32&& Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)) != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(Convert.ToInt32(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
|
}
|
|
|
else if (item.DataType == DbType.Decimal&& Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)) != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(Convert.ToDouble(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 2014-01-26
|
|
|
/// 石志超
|
|
|
/// </summary>
|
|
|
public string Export<T>(string HeaderName, List<T> lst, ReportPrint.TableColumnCollection Columns) where T : class
|
|
|
{
|
|
|
Type t = typeof(T);
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
//sheet.PrintSetup.PaperSize = 9;
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
|
|
|
HSSFRow dataRow = null;
|
|
|
int RowIndex = 0;
|
|
|
|
|
|
if (!string.IsNullOrEmpty(HeaderName))
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(0);
|
|
|
dataCell.SetCellValue(HeaderName);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
sheet.AddMergedRegion(new Region(0, 0, 0, Columns.Count - 1));
|
|
|
RowIndex++;
|
|
|
}
|
|
|
//填充表头
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(RowIndex);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
RowIndex++;
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < lst.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + RowIndex);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(
|
|
|
DataConvert.Format(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
|
|
|
public string Export(DataTable dt1, ReportPrint.TableColumnCollection Columns1, DataTable dt2, ReportPrint.TableColumnCollection Columns2)
|
|
|
{
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
|
|
|
DataTable tb = dt1;
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
//填充表头
|
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns1)
|
|
|
{
|
|
|
int index = Columns1.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns1)
|
|
|
{
|
|
|
int j = Columns1.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns1)
|
|
|
{
|
|
|
int index = Columns1.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
if (dt2 != null)
|
|
|
{
|
|
|
|
|
|
|
|
|
HSSFSheet sheet2 = (HSSFSheet)workbook.CreateSheet("Sheet2");
|
|
|
//填充表头
|
|
|
HSSFRow dataRow2 = (HSSFRow)sheet2.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns2)
|
|
|
{
|
|
|
int index = Columns2.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow2.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < dt2.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow2 = (HSSFRow)sheet2.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns2)
|
|
|
{
|
|
|
int j = Columns2.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow2.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(dt2.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(dt2.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns2)
|
|
|
{
|
|
|
int index = Columns2.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet2.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet2.AutoSizeColumn(index); }
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
|
|
|
public string Export(string HeaderName, DataTable tb, ReportPrint.TableColumnCollection Columns)
|
|
|
{
|
|
|
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
HSSFRow dataRow = null;
|
|
|
HSSFCell dataCell = null;
|
|
|
int RowIndex = 0;
|
|
|
if (HeaderName != "")
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(0);
|
|
|
dataCell.SetCellValue(HeaderName);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
sheet.AddMergedRegion(new Region(0, 0, 0, Columns.Count - 1));
|
|
|
RowIndex++;
|
|
|
}
|
|
|
//填充表头
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(RowIndex);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
RowIndex++;
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + RowIndex);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 导出到指定文件
|
|
|
public void ExportFile(string FilePath, DataTable tb, ReportPrint.TableColumnCollection Columns)
|
|
|
{
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
//填充表头
|
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
|
HSSFCell dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
//保存
|
|
|
using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
|
|
|
{
|
|
|
workbook.Write(fs);
|
|
|
}
|
|
|
workbook.Dispose();
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region ConvertHSSFWorkbook
|
|
|
public HSSFWorkbook ConvertHSSFWorkbook(DataTable tb, ReportPrint.TableColumnCollection Columns, int StartRowIndex = 0)
|
|
|
{
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
|
sheet.PrintSetup.Scale = 100;
|
|
|
HSSFRow dataRow = null;
|
|
|
HSSFCell dataCell = null;
|
|
|
if (StartRowIndex > 0)
|
|
|
{
|
|
|
for (int i = 0; i < StartRowIndex; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i);
|
|
|
for (int j = 0; j < Columns.Count; j++)
|
|
|
{
|
|
|
dataRow.CreateCell(j).SetCellValue("");
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
//填充表头
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(StartRowIndex);
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
|
dataCell.SetCellValue(item.Caption);
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
|
|
|
//填充内容
|
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
|
{
|
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + StartRowIndex + 1);
|
|
|
dataCell = null;
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int j = Columns.IndexOf(item);
|
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
|
if (item.Format != "")
|
|
|
{
|
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
|
}
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
|
//边框
|
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
|
//边框颜色
|
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
|
}
|
|
|
}
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
int index = Columns.IndexOf(item);
|
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
|
else { sheet.AutoSizeColumn(index); }
|
|
|
}
|
|
|
return workbook;
|
|
|
}
|
|
|
#endregion
|
|
|
///<summary>
|
|
|
///读取csv格式的Excel文件的方法
|
|
|
///</ummary>
|
|
|
///<param name="path">待读取Excel的全路径</param>
|
|
|
///<returns></returns>
|
|
|
public DataTable GetdataFromCVS(string fileName)
|
|
|
{
|
|
|
|
|
|
DataTable dt = new DataTable();
|
|
|
|
|
|
StreamReader sr = new StreamReader(fileName, System.Text.Encoding.GetEncoding("GB2312"));
|
|
|
|
|
|
string strTitle = sr.ReadLine();
|
|
|
|
|
|
string[] strColumTitle = strTitle.Split(','); //CVS 文件默认以逗号隔开
|
|
|
|
|
|
for (int i = 0; i < strColumTitle.Length; i++)
|
|
|
{
|
|
|
|
|
|
dt.Columns.Add(strColumTitle[i].Replace("\"", "").Trim());
|
|
|
|
|
|
}
|
|
|
//string[] aryline;
|
|
|
while (!sr.EndOfStream)
|
|
|
{
|
|
|
|
|
|
string strTest = sr.ReadLine();
|
|
|
|
|
|
string[] strTestAttribute = strTest.Split(',');
|
|
|
|
|
|
DataRow dr = dt.NewRow();
|
|
|
|
|
|
for (int i = 0; i < strColumTitle.Length; i++)
|
|
|
{
|
|
|
|
|
|
dr[i] = strTestAttribute[i].Replace("=", "").Replace("\"", "").Replace("'", "").Trim();
|
|
|
|
|
|
}
|
|
|
|
|
|
dt.Rows.Add(dr);
|
|
|
|
|
|
}
|
|
|
|
|
|
return dt;
|
|
|
|
|
|
}
|
|
|
///<summary>
|
|
|
///读取csv格式的Excel文件的方法
|
|
|
///</ummary>
|
|
|
///<param name="path">待读取Excel的全路径</param>
|
|
|
///<returns></returns>
|
|
|
public DataTable GetdataFromCVS2(string fileName)
|
|
|
{
|
|
|
|
|
|
DataTable dt = new DataTable();
|
|
|
|
|
|
StreamReader sr = new StreamReader(fileName, System.Text.Encoding.GetEncoding("GB2312"));
|
|
|
|
|
|
string strTitle = sr.ReadLine();
|
|
|
|
|
|
string[] strColumTitle = strTitle.Split(','); //CVS 文件默认以逗号隔开
|
|
|
|
|
|
for (int i = 0; i < strColumTitle.Length; i++)
|
|
|
{
|
|
|
|
|
|
dt.Columns.Add(strColumTitle[i].Replace("\"", "").Trim());
|
|
|
|
|
|
}
|
|
|
//string[] aryline;
|
|
|
while (!sr.EndOfStream)
|
|
|
{
|
|
|
|
|
|
string strTest = sr.ReadLine();
|
|
|
|
|
|
string[] strTestAttribute = strToAry(strTest);//请注意：此处变了strTest.Split(',');
|
|
|
|
|
|
DataRow dr = dt.NewRow();
|
|
|
|
|
|
for (int i = 0; i < strColumTitle.Length; i++)
|
|
|
{
|
|
|
|
|
|
dr[i] = strTestAttribute[i].Replace("=", "").Replace("\"\"", "\"").Trim();
|
|
|
|
|
|
}
|
|
|
|
|
|
dt.Rows.Add(dr);
|
|
|
|
|
|
}
|
|
|
|
|
|
return dt;
|
|
|
|
|
|
}
|
|
|
private static string[] strToAry(string strLine)
|
|
|
{
|
|
|
string strItem = "";
|
|
|
int iFenHao = 0;
|
|
|
System.Collections.ArrayList lstStr = new System.Collections.ArrayList();
|
|
|
for (int i = 0; i < strLine.Length; i++)
|
|
|
{
|
|
|
string strA = strLine.Substring(i, 1);
|
|
|
if (strA == "\"")
|
|
|
{
|
|
|
iFenHao = iFenHao + 1;
|
|
|
}
|
|
|
if (iFenHao == 2)
|
|
|
{
|
|
|
iFenHao = 0;
|
|
|
}
|
|
|
if (strA == "," && iFenHao == 0)
|
|
|
{ lstStr.Add(strItem); strItem = ""; }
|
|
|
else
|
|
|
{ strItem = strItem + strA; }
|
|
|
}
|
|
|
if (strItem.Length > 0)
|
|
|
lstStr.Add(strItem);
|
|
|
return (String[])lstStr.ToArray(typeof(string));
|
|
|
}
|
|
|
|
|
|
#region 写入CSV，附带路径方法
|
|
|
|
|
|
public string WriteCSV(bool hasHeader, DataTable dtOutputCSV, TableColumnCollection Columns, string _splitChar)
|
|
|
{
|
|
|
|
|
|
if (dtOutputCSV == null)
|
|
|
{
|
|
|
throw new ArgumentNullException("Argument(dtOutputCSV) is null!");
|
|
|
}
|
|
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
|
|
|
|
if (hasHeader)
|
|
|
{
|
|
|
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
sb.Append("\"" + item.Caption + "\"");
|
|
|
sb.Append(",");
|
|
|
|
|
|
}
|
|
|
|
|
|
sb.Remove(sb.Length - 1, 1);
|
|
|
sb.Append(Environment.NewLine);
|
|
|
}
|
|
|
for (int i = 0; i < dtOutputCSV.Rows.Count; i++)
|
|
|
{
|
|
|
foreach (ReportPrint.TableColumn item in Columns)
|
|
|
{
|
|
|
|
|
|
|
|
|
for (int j = 0; j < dtOutputCSV.Columns.Count; j++)
|
|
|
{
|
|
|
string str = dtOutputCSV.Rows[i][j].ToString();
|
|
|
if (str.Contains(",") || str.Contains("\"") || str.Contains("\r") || str.Contains("\n")) //含逗号 冒号 换行符的需要放到引号中
|
|
|
{
|
|
|
str = string.Format("\"{0}\"", str);
|
|
|
}
|
|
|
if (dtOutputCSV.Columns[j].ColumnName == item.Name)
|
|
|
sb.Append("\"" + str + "\"" + ",");
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
sb.Remove(sb.Length - 1, 1);
|
|
|
sb.Append(Environment.NewLine);
|
|
|
}
|
|
|
string filename = Guid.NewGuid().ToString() + ".csv";
|
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
|
|
|
|
|
|
|
using (TextWriter tw = new StreamWriter(ServerFileName, false))
|
|
|
{
|
|
|
tw.Write(sb.ToString());
|
|
|
|
|
|
tw.Flush();
|
|
|
tw.Close();
|
|
|
}
|
|
|
return "ServerCookies/" + filename;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 导入
|
|
|
public DataTable ImportExcel(string fileName)
|
|
|
{
|
|
|
HSSFWorkbook hssfworkbook;
|
|
|
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
|
|
|
{
|
|
|
hssfworkbook = new HSSFWorkbook(file);
|
|
|
}
|
|
|
ISheet sheet = hssfworkbook.GetSheetAt(0);
|
|
|
IEnumerator rows = sheet.GetRowEnumerator();
|
|
|
|
|
|
DataTable dt = new DataTable();
|
|
|
IRow row;
|
|
|
|
|
|
rows.MoveNext();
|
|
|
row = (HSSFRow)rows.Current;
|
|
|
int len = row.LastCellNum;
|
|
|
for (int i = 0; i < len; i++)
|
|
|
{
|
|
|
ICell cell = row.GetCell(i);
|
|
|
if (cell != null)
|
|
|
{
|
|
|
dt.Columns.Add(cell.ToString().Trim());
|
|
|
}
|
|
|
}
|
|
|
|
|
|
while (rows.MoveNext())
|
|
|
{
|
|
|
row = (HSSFRow)rows.Current;
|
|
|
DataRow dr = dt.NewRow();
|
|
|
|
|
|
for (int i = 0; i < len; i++)
|
|
|
{
|
|
|
ICell cell = row.GetCell(i);
|
|
|
if (cell == null)
|
|
|
{
|
|
|
dr[i] = null;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
//读取Excel格式，根据格式读取数据类型
|
|
|
switch (cell.CellType)
|
|
|
{
|
|
|
case CellType.BLANK: //空数据类型处理
|
|
|
dr[i] = "";
|
|
|
break;
|
|
|
case CellType.STRING: //字符串类型
|
|
|
dr[i] = cell.StringCellValue;
|
|
|
break;
|
|
|
case CellType.NUMERIC: //数字类型
|
|
|
if (DateUtil.IsCellDateFormatted(cell))//日期类型
|
|
|
{
|
|
|
dr[i] = cell.DateCellValue;
|
|
|
}
|
|
|
else//其他数字类型
|
|
|
{
|
|
|
dr[i] = cell.NumericCellValue;
|
|
|
}
|
|
|
|
|
|
break;
|
|
|
case CellType.FORMULA:
|
|
|
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
|
|
|
dr[i] = e.Evaluate(cell).StringValue;
|
|
|
break;
|
|
|
default:
|
|
|
dr[i] = "";
|
|
|
break;
|
|
|
}
|
|
|
|
|
|
//if (cell.CellType == CellType.FORMULA || cell.CellType == CellType.NUMERIC)
|
|
|
//{
|
|
|
// dr[i] = cell.DateCellValue;
|
|
|
//}
|
|
|
//else
|
|
|
//{
|
|
|
|
|
|
|
|
|
// dr[i] = cell.ToString().Trim();
|
|
|
//}
|
|
|
}
|
|
|
}
|
|
|
dt.Rows.Add(dr);
|
|
|
}
|
|
|
|
|
|
return dt;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 导入
|
|
|
public DataTable ImportExcel2(string fileName)
|
|
|
{
|
|
|
HSSFWorkbook hssfworkbook;
|
|
|
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
|
|
|
{
|
|
|
hssfworkbook = new HSSFWorkbook(file);
|
|
|
}
|
|
|
ISheet sheet = hssfworkbook.GetSheetAt(0);
|
|
|
IEnumerator rows = sheet.GetRowEnumerator();
|
|
|
|
|
|
DataTable dt = new DataTable();
|
|
|
IRow row;
|
|
|
|
|
|
rows.MoveNext();
|
|
|
row = (HSSFRow)rows.Current;
|
|
|
int len = row.LastCellNum;
|
|
|
for (int i = 0; i < len; i++)
|
|
|
{
|
|
|
ICell cell = row.GetCell(i);
|
|
|
if (cell != null)
|
|
|
{
|
|
|
dt.Columns.Add(cell.ToString().Trim()+i.ToString());
|
|
|
}
|
|
|
}
|
|
|
|
|
|
while (rows.MoveNext())
|
|
|
{
|
|
|
row = (HSSFRow)rows.Current;
|
|
|
DataRow dr = dt.NewRow();
|
|
|
|
|
|
for (int i = 0; i < len; i++)
|
|
|
{
|
|
|
ICell cell = row.GetCell(i);
|
|
|
if (cell == null)
|
|
|
{
|
|
|
dr[i] = null;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
//读取Excel格式，根据格式读取数据类型
|
|
|
switch (cell.CellType)
|
|
|
{
|
|
|
case CellType.BLANK: //空数据类型处理
|
|
|
dr[i] = "";
|
|
|
break;
|
|
|
case CellType.STRING: //字符串类型
|
|
|
dr[i] = cell.StringCellValue;
|
|
|
break;
|
|
|
case CellType.NUMERIC: //数字类型
|
|
|
if (DateUtil.IsCellDateFormatted(cell))//日期类型
|
|
|
{
|
|
|
dr[i] = cell.DateCellValue;
|
|
|
}
|
|
|
else//其他数字类型
|
|
|
{
|
|
|
dr[i] = cell.NumericCellValue;
|
|
|
}
|
|
|
|
|
|
break;
|
|
|
case CellType.FORMULA:
|
|
|
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
|
|
|
dr[i] = e.Evaluate(cell).StringValue;
|
|
|
break;
|
|
|
default:
|
|
|
dr[i] = "";
|
|
|
break;
|
|
|
}
|
|
|
|
|
|
//if (cell.CellType == CellType.FORMULA || cell.CellType == CellType.NUMERIC)
|
|
|
//{
|
|
|
// dr[i] = cell.DateCellValue;
|
|
|
//}
|
|
|
//else
|
|
|
//{
|
|
|
|
|
|
|
|
|
// dr[i] = cell.ToString().Trim();
|
|
|
//}
|
|
|
}
|
|
|
}
|
|
|
dt.Rows.Add(dr);
|
|
|
}
|
|
|
|
|
|
return dt;
|
|
|
}
|
|
|
#endregion
|
|
|
/// <summary>
|
|
|
/// 解析Excel
|
|
|
/// </summary>
|
|
|
/// <param name="filePath"></param>
|
|
|
/// <param name="name"></param>
|
|
|
/// <returns></returns>
|
|
|
public DataSet LoadDataFromExcel(string filePath, string name)
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
string strConn;
|
|
|
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
|
|
|
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
|
|
|
OleDbConnection OleConn = new OleDbConnection(strConn);
|
|
|
OleConn.Open();
|
|
|
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称，比如sheet2，等等
|
|
|
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
|
|
|
DataSet OleDsExcle = new DataSet();
|
|
|
OleDaExcel.Fill(OleDsExcle, name);
|
|
|
OleConn.Close();
|
|
|
return OleDsExcle;
|
|
|
}
|
|
|
catch (Exception err)
|
|
|
{
|
|
|
//MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
