#region DataReader 的数据导出到Excle 中
//public string Exports(string str_sql)
//{
// string destFilePath = "";
// Guid guid = Guid.NewGuid();
// destFilePath = guid.ToString();
// string path = Common.GetServicePath();//返回服务器路径
// string serverpath = path + destFilePath + ".xlsx"; //在服务器端定义Excle 让客户端进行进行下载
// StreamWriter str_ExcleReader = new StreamWriter(serverpath, false, Encoding.Default);
// OracleConnection connection = new OracleConnection(Common.GetConnectStr());
// connection.Open();
// OracleCommand cmd = new OracleCommand(str_sql, connection);
// OracleDataReader Reader = cmd.ExecuteReader();
// for (int i = 0; i < Reader.FieldCount; ++i)
// {
// str_ExcleReader.Write(Reader.GetName(i));
// str_ExcleReader.Write('\t');
// }
// str_ExcleReader.Write("\r\n");
// object[] values = new object[Reader.FieldCount];
// while (Reader.Read())
// {
// Reader.GetValues(values);
// for (int i = 0; i < values.Length; ++i)
// {
// str_ExcleReader.Write(values[i].ToString());
// str_ExcleReader.Write('\t');
// }
// str_ExcleReader.Write("\r\n");
// }
// str_ExcleReader.Flush();
// str_ExcleReader.Close();
// Reader.Close();
// connection.Close();
// return ReturnData.WriteXml("<data>" + serverpath + "</data>");
//}
#endregion
#region 根据查询条件查询出的数据导出Excle2010中
/// <summary>
/// 根据查询条件把查询出的数据导出Excle2010中
/// </summary>
/// <param name="stringSelect"></param>
public string ExportTO(string defaultFileName)
{
string reslut = "true", info = "";
try
{
//注意using的用法；
//OracleDataReader dataReader = DataReader();
//ExportToWorkbook(dataReader);
//info = ExportToWorkbook(dataReader);
//dataReader.Close();
}
catch (Exception ex)
{
reslut = "false";
info = ex.Message;
}
return ReturnData.WriteXml("<result>" + reslut + "</result>" + reslut + "<info>" + info + "</info>");
}
/// <summary>
/// 该方法实现dataReader读取的内容导入excel；
/// </summary>
/// <param name="dataReader"></param>
private static string ExportToWorkbook(OracleDataReader dataReader)
{
var excelApplication = new Microsoft.Office.Interop.Excel.Application();
try
{
Worksheet workSheet = CreateWorksheet(excelApplication);
WriteHeader(workSheet, dataReader);
int rowCount = WriteBody(workSheet, dataReader);
//格式化表；
FormatAsTable(workSheet, rowCount, dataReader);
string destFilePath = "";
Guid guid = Guid.NewGuid();
destFilePath = guid.ToString();
string path = Common.GetServicePath();//返回服务器路径
string serverpath = path + destFilePath + ".xlsx"; //在服务器端定义Excle 让客户端进行进行下载
// excelApplication.Save("数据库表.xls");
excelApplication.Save(serverpath);
excelApplication.Visible = true;
return serverpath;
}
//保证退出；
finally
{
excelApplication.Quit();
}
}
/// <summary>
/// 创建工作薄和脚本
/// </summary>
/// <param name="excelApplication"></param>
/// <returns></returns>
private static Worksheet CreateWorksheet(Microsoft.Office.Interop.Excel.Application excelApplication)
{
Workbook workBook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet workSheet = (Worksheet)workBook.Worksheets[1];
return workSheet;
}
/// <summary>
/// 表的列头
/// </summary>
/// <param name="workSheet"></param>
private static void WriteHeader(Worksheet workSheet, OracleDataReader Reader)
{
#region Excle的表头添加数据
workSheet.Cells[1, 1] = "主编号";
workSheet.Cells[1, 2] = "次级编号";
workSheet.Cells[1, 3] = "资产名称";
workSheet.Cells[1, 4] = "规格型号";
workSheet.Cells[1, 5] = "技术参数";
#region 隐藏
workSheet.Cells[1, 6] = "自编号";
workSheet.Cells[1, 7] = "资产类别";
workSheet.Cells[1, 8] = "类别名称";
workSheet.Cells[1, 1] = "成本中心";
workSheet.Cells[1, 2] = "资产来源";
workSheet.Cells[1, 3] = "资产名称";
workSheet.Cells[1, 4] = "规格型号";
workSheet.Cells[1, 5] = "技术参数";
workSheet.Cells[1, 6] = "数量";
workSheet.Cells[1, 7] = "计量单位";
workSheet.Cells[1, 8] = "计量单位名称";
workSheet.Cells[1, 9] = "使用状况";
workSheet.Cells[1, 10] = "经济用途";
workSheet.Cells[1, 11] = "技术状态";
workSheet.Cells[1, 12] = "设计能力";
workSheet.Cells[1, 13] = "能力单位";
workSheet.Cells[1, 14] = "生产装置";
workSheet.Cells[1, 15] = "使用单位";
workSheet.Cells[1, 16] = "使用单位名称";
workSheet.Cells[1, 17] = "供应商";
workSheet.Cells[1, 18] = "供应商名称";
workSheet.Cells[1, 19] = "生产厂家";
workSheet.Cells[1, 20] = "生产日期";
workSheet.Cells[1, 21] = "资本化日期";
workSheet.Cells[1, 22] = "投产日期";
workSheet.Cells[1, 23] = "资产原值";
workSheet.Cells[1, 24] = "累计折旧";
workSheet.Cells[1, 25] = "减值准备";
workSheet.Cells[1, 26] = "账面折旧年限";
workSheet.Cells[1, 27] = "已用年限";
workSheet.Cells[1, 28] = "原资产编号";
workSheet.Cells[1, 29] = "原资产次级编号";
workSheet.Cells[1, 30] = "累计折旧";
workSheet.Cells[1, 31] = "减值准备";
#endregion
#endregion
for (int i = 0; i < Reader.FieldCount; ++i)
{
workSheet.Cells[1, i] = Reader.GetName(i);
}
}
/// <summary>
/// dataReader内容写入worksheet；
/// </summary>
/// <param name="workSheet"></param>
/// <param name="dataReader"></param>
/// <returns></returns>
private static int WriteBody(Worksheet workSheet, OracleDataReader dataReader)
{
int rowIndex = 2;
object[] values = new object[dataReader.FieldCount];
while (dataReader.Read())
{
#region 给定值添加内容
workSheet.Cells[rowIndex, 1] = dataReader["主编号"];
workSheet.Cells[rowIndex, 2] = dataReader["次级编号"];
workSheet.Cells[rowIndex, 3] = dataReader["资产名称"];
workSheet.Cells[rowIndex, 4] = dataReader["规格型号"];
workSheet.Cells[rowIndex, 5] = dataReader["技术参数"];
#region 隐藏
workSheet.Cells[rowIndex, 6] = dataReader["自编号"];
workSheet.Cells[rowIndex, 7] = dataReader["资产类别"];
workSheet.Cells[rowIndex, 8] = dataReader["类别名称"];
workSheet.Cells[rowIndex, 9] = dataReader["成本中心"];
workSheet.Cells[rowIndex, 10] = dataReader["资产来源"];
workSheet.Cells[rowIndex, 11] = dataReader["数量"];
workSheet.Cells[rowIndex, 12] = dataReader["计量单位"];
workSheet.Cells[rowIndex, 13] = dataReader["计量单位名称"];
workSheet.Cells[rowIndex, 14] = dataReader["使用状况"];
workSheet.Cells[rowIndex, 15] = dataReader["经济用途"];
workSheet.Cells[rowIndex, 16] = dataReader["技术状态"];
workSheet.Cells[rowIndex, 17] = dataReader["设计能力"];
workSheet.Cells[rowIndex, 18] = dataReader["能力单位"];
workSheet.Cells[rowIndex, 19] = dataReader["生产装置"];
workSheet.Cells[rowIndex, 20] = dataReader["使用单位"];
workSheet.Cells[rowIndex, 21] = dataReader["使用单位名称"];
workSheet.Cells[rowIndex, 22] = dataReader["供应商"];
workSheet.Cells[rowIndex, 23] = dataReader["生产厂家"];
workSheet.Cells[rowIndex, 24] = dataReader["生产日期"];
workSheet.Cells[rowIndex, 25] = dataReader["投产日期"];
workSheet.Cells[rowIndex, 26] = dataReader["资产原值"];
workSheet.Cells[rowIndex, 27] = dataReader["累计折旧"];
workSheet.Cells[rowIndex, 28] = dataReader["减值准备"];
workSheet.Cells[rowIndex, 29] = dataReader["原资产编号"];
workSheet.Cells[rowIndex, 30] = dataReader["资产原值"];
workSheet.Cells[rowIndex, 31] = dataReader["累计折旧"];
workSheet.Cells[rowIndex, 31] = dataReader["减值准备"];
#endregion
rowIndex++;
#endregion
dataReader.GetValues(values);
for (int i = 0; i < values.Length; ++i)
{
workSheet.Cells[rowIndex, i] = dataReader[values[i].ToString()];
}
}
return rowIndex;
}
private static void FormatAsTable(Worksheet workSheet, int totalRows, OracleDataReader reader)
{
Range topLeft = (Range)workSheet.Cells[1, 1];
Range bottomRight = (Range)workSheet.Cells.get_Item(totalRows - 1, reader.FieldCount);
Range range = workSheet.get_Range(topLeft, bottomRight);
//listObject对象和方法；
ListObject listObject = workSheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes, null);
listObject.TableStyle = "TableStyleMedium10";
}
#endregion