下面利用最wps2015实现了一个简单的导入到出，参考http://blog.163.com/felex_cheng@126/blog/static/410470052013818325357/文章，也可也参考一下wsp二次开发文档http://www.wps.cn/wpsapi/funcapilist/page-1.htm，相应的dll引用如下图：
====================
1. 打开ET文件
ET.Application etApp;
ET.workbook etbook;
ET.Worksheet etsheet;
ET.Range etrange;
etApp = new ET.Application();
etbook = new ET.workbook();
//获取工作表格
etbook = (ET.workbook)etApp.Workbooks.Open(strEtFPath, null, null, null, null, null, null, null, null, null, null, null, null);
//获取第一个表格
etsheet = (ET.Worksheet)etbook.Worksheets.get_Item(1);
//获取数据区域
etrange = (ET.Range)etsheet.UsedRange;
2.关闭ET文件
[csharp] view plain copy
<code class="language-csharp">ET.Application etApp;
ET.workbook etbook;
ET.Worksheet etsheet;
ET.Range etrange;
etbook.Close(true, strEtFPath, System.Reflection.Missing.Value);
etApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(etrange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);</code>
3.获取ET文件的行数于列数
int iRow = etrange.Rows.Count;
int iColumn = etrange.Columns.Count;
4.读取指定位置的数据
int iRowCount = 0;
int iColumnCount = 0;
strData = ((ET.Range)etrange.get_Item(iRowCount, iColumnCount)).Text;
5.创建ET文件
ET.Application etApp;
ET.workbook etbook;
ET.Workbooks etbooks;
ET.Worksheet etsheet;
etApp = new ET.Application();
[csharp] view plain copy
<code class="language-csharp">etbooks = etApp.Workbooks;
etbook = etbooks.Add(System.Reflection.Missing.Value);
etsheet = (ET.Worksheet)etbook.ActiveSHeet;
</code>
---------------------
============================
1. 首先将wps的相关COM组件添加至引用。project -> add reference -> com-> Kingsoft ET 2.0 Object Library.
2. 代码中添加using KSO; using ET;
3. 打开xls文件的相关代码：
ET.Application etApp;
ET.workbook etbook;
ET.Worksheet etsheet ;
ET.Range etrange;
//获取工作表表格
etApp = new ET.Application();
etbook = (ET.workbook)etApp.Workbooks.Open(@"c:\file.xls");
//获取数据区域
etsheet = (ET.Worksheet)etbook.Worksheets.get_Item(1);
//获取数据区域
etrange = (ET.Range)etsheet.UsedRange;
4. 读取某单元格的数据内容：
string strData = ((ET.Range)etrange.get_Item(i, j)).Text;
5. 写入某单元格的数据内容：
((ET.Range)etrange.get_Item(i, j)).Value = strData;
6. 关闭文件及相关资源：
etbook.Close();
etApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(etrange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);
以下代码是可以运行的
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using KSO;
using ET;
namespace TestWPS
{
public partial class Form1 : Form
{
ET.Application objApp = null;
ET._Workbook objWorkBook = null;
ET.Worksheet objWorkSheet = null;
public Form1()
{
InitializeComponent();
}
private void exportWPS_excel(DataTable dt)
{
object missing = Type.Missing;
ET.Range objRange = null;
try
{
objApp = new ET.Application();
objWorkBook = objApp.Workbooks.Add(Type.Missing);
objWorkSheet = objWorkBook.ActiveSheet;
//设置标题
objWorkSheet.get_Range("A1", "G1").Merge(true); //先进行单元合并
objRange = objWorkSheet.get_Range("A1", "A1");
objRange.Value2 = "表 格 示 例"; //设置合并后的单元格的文本
objRange.RowHeight = 40; //设置行高
//设置字体
objRange.Font.Name = "宋体";
objRange.Font.Size = 20;
objRange.Font.Bold = true;
objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter; //设置字体对其方向
objRange = objWorkSheet.get_Range("A2", "G7");
objRange.Borders.LineStyle = ET.ETLineStyle.etContinuous;
objRange.Borders.Weight = ET.ETBorderWeight.etThin;
objRange.RowHeight = 17;
objRange.Borders.Color = 0; //设置边框的颜色，颜色的值可以按照B,G,R的方式合成
//设置表格的外边框，加粗
objRange.Borders[ET.ETBorderIndex.etEdgeBottom].Weight = ET.ETBorderWeight.etMedium;
objRange.Borders[ET.ETBorderIndex.etEdgeLeft].Weight = ET.ETBorderWeight.etMedium;
objRange.Borders[ET.ETBorderIndex.etEdgeRight].Weight = ET.ETBorderWeight.etMedium;
objRange.Borders[ET.ETBorderIndex.etEdgeTop].Weight = ET.ETBorderWeight.etMedium;
objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
objRange.Font.Name = "宋体";
objRange.Font.Size = 10;
string name = dt.Rows[0][0].ToString();
objWorkSheet.get_Range("A2", "A2").Value2 = "姓 名";
objWorkSheet.get_Range("A2", "A2").ColumnWidth = 60;
objWorkSheet.get_Range("B2", "B2").ColumnWidth = 75;
objWorkSheet.get_Range("B2", "B2").Value2 = name;
objWorkSheet.get_Range("C2", "C2").Value2 = "性 别";
objWorkSheet.get_Range("C2", "C2").ColumnWidth = 60;
objWorkSheet.get_Range("D2", "D2").ColumnWidth = 75;
objWorkSheet.get_Range("D2", "D2").Value2 = dt.Rows[0][1].ToString();
objWorkSheet.get_Range("E2", "E2").Value2 = "出生年月";
objWorkSheet.get_Range("E2", "E2").ColumnWidth = 60;
objWorkSheet.get_Range("F2", "F2").ColumnWidth = 75;
objWorkSheet.get_Range("F2", "F2").Value2 = dt.Rows[0][2].ToString();
objWorkSheet.get_Range("G2", "G7").Merge(false);
objWorkSheet.get_Range("G2", "G2").ColumnWidth = 80;
objWorkSheet.get_Range("G2", "G2").Value2 = "照片";
objWorkSheet.get_Range("A3", "A3").Value2 = "民 族";
objWorkSheet.get_Range("B3", "B3").Value2 = dt.Rows[0][3].ToString();
objWorkSheet.get_Range("C3", "C3").Value2 = "政治面貌";
objWorkSheet.get_Range("D3", "D3").Value2 = dt.Rows[0][4].ToString();
objWorkSheet.get_Range("E3", "E3").Value2 = "籍 贯";
objWorkSheet.get_Range("F3", "F3").Value2 = dt.Rows[0][5].ToString();
objWorkSheet.get_Range("A4", "A4").Value2 = "学 历";
objWorkSheet.get_Range("B4", "B4").Value2 = dt.Rows[0][6].ToString();
objWorkSheet.get_Range("C4", "C4").Value2 = "固定电话";
objWorkSheet.get_Range("D4", "D4").Value2 = dt.Rows[0][7].ToString();
objWorkSheet.get_Range("E4", "E4").Value2 = "移动电话";
objWorkSheet.get_Range("F4", "F4").Value2 = dt.Rows[0][8].ToString();
objWorkSheet.get_Range("A5", "A5").Value2 = "毕业院校";
objWorkSheet.get_Range("B5", "F5").Merge(false);
objWorkSheet.get_Range("B5", "B5").Value2 = dt.Rows[0][9].ToString();
objWorkSheet.get_Range("A6", "A6").Value2 = "家庭住址";
objWorkSheet.get_Range("B6", "F6").Merge(false);
objWorkSheet.get_Range("B6", "B6").Value2 = dt.Rows[0][10].ToString();
objWorkSheet.get_Range("A7", "A7").Value2 = "Email";
objWorkSheet.get_Range("B7", "F7").Merge(false);
objWorkSheet.get_Range("B7", "B7").Value2 = dt.Rows[0][11].ToString();
objWorkBook.SaveAs(string.Format("D:\\{0}.xls", name), missing, missing, missing, missing, missing, ET.ETSaveAsAccessMode.etExclusive, missing, missing, missing, missing);
objWorkBook.Close(missing, missing, missing);
}
finally
{
ReleaseComObject(objRange);
ReleaseComObject(objWorkSheet);
ReleaseComObject(objWorkBook);
}
}
private void ReleaseComObject(object obj)
{
if (obj != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
}
/// <summary>
/// 将DataTable的数据导出显示为报表(使用WPS)
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public string OutputWPSExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
DateTime beforeTime = DateTime.Now;
object missing = Type.Missing;
ET.Range objRange = null;
string filename = "";
try
{
objApp = new ET.Application();
objWorkBook = objApp.Workbooks.Add(Type.Missing);
objWorkSheet = objWorkBook.ActiveSheet;
int rowIndex = 4;
int colIndex = 1;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
//excel.Cells[4, colIndex]
objWorkSheet.Cells[4,colIndex] = col.ColumnName;
//设置标题格式为居中对齐
Range range = objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, colIndex),(object) objWorkSheet.Cells.get_Item(4, colIndex));
range.Font.Bold = true;
range.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
range.Select();
//objWorkSheet.get_Range(objWorkSheet.Cells[4, colIndex], objWorkSheet.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
objWorkSheet.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(rowIndex, colIndex), (object)objWorkSheet.Cells.get_Item(rowIndex, colIndex)).HorizontalAlignment = ET.ETHAlign.etHAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
Range txtRange = (Range)objWorkSheet.Cells[rowIndex, colIndex];
txtRange.NumberFormatLocal = "@";
objWorkSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(rowIndex, colIndex),(object) objWorkSheet.Cells.get_Item(rowIndex, colIndex)).HorizontalAlignment = ET.ETHAlign.etHAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
objWorkSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
objWorkSheet.Cells[rowSum, 2] = "合计";
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(rowSum, 2), (object)objWorkSheet.Cells.get_Item(rowSum, 2)).HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
//取得整个报表的标题
objWorkSheet.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(2, 2), (object)objWorkSheet.Cells.get_Item(2, 2)).Font.Bold = true;
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(2, 2), (object)objWorkSheet.Cells.get_Item(2, 2)).Font.Size = 22;
//设置报表表格为最适应宽度
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, 2), (object)objWorkSheet.Cells.get_Item(rowSum, colIndex)).Select();
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, 2), (object)objWorkSheet.Cells.get_Item(rowSum, colIndex)).Columns.AutoFit();
设置整个报表的标题为跨列居中
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(2, 2), (object)objWorkSheet.Cells.get_Item(2, colIndex)).Select();
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(2, 2), (object)objWorkSheet.Cells.get_Item(2, colIndex)).HorizontalAlignment = ET.ETHAlign.etHAlignCenterAcrossSelection;
绘制边框
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, 2), (object)objWorkSheet.Cells.get_Item(rowSum, colIndex)).Borders.LineStyle = ETLineStyle.etContinuous;
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, 2), (object)objWorkSheet.Cells.get_Item(rowSum, 2)).Borders[ET.ETBorderIndex.etEdgeLeft].Weight = ET.ETBorderWeight.etThick;//设置左边线加粗
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, 2), (object)objWorkSheet.Cells.get_Item(4, colIndex)).Borders[ET.ETBorderIndex.etEdgeTop].Weight = ET.ETBorderWeight.etThick;//设置上边线加粗
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, colIndex), (object)objWorkSheet.Cells.get_Item(rowSum, colIndex)).Borders[ET.ETBorderIndex.etEdgeRight].Weight = ET.ETBorderWeight.etThick;//设置右边线加粗
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(rowSum, 2), (object)objWorkSheet.Cells.get_Item(rowSum, colIndex)).Borders[ET.ETBorderIndex.etEdgeBottom].Weight = ET.ETBorderWeight.etThick;//设置下边线加粗
DateTime afterTime = DateTime.Now;
filename =FilePath+ strTitle + "_" + DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
//保存文件
objWorkBook.SaveAs(filename, missing, missing, missing, missing, missing, ET.ETSaveAsAccessMode.etExclusive, missing, missing, missing, missing);
objWorkBook.Close(missing, missing, missing);
}
finally
{
ReleaseComObject(objRange);
ReleaseComObject(objWorkSheet);
ReleaseComObject(objWorkBook);
}
return filename;
}
private void button1_Click(object sender, EventArgs e)
{
//DateTime dt = DateTime.Now;
try
{
DataTable dt = new DataTable();
for (int i = 0; i < 12; i++)
{
dt.Columns.Add(i.ToString(), typeof(string));
}
DataRow dr = dt.NewRow();
for (int j = 0; j < 12; j++)
{
dr[j] = j.ToString();
}
dt.Rows.Add(dr);
//exportWPS_excel(dt);
string s= OutputWPSExcel(dt, "测试报表", "d:\\");
MessageBox.Show(s);
}
finally
{
}
}
}
}
1545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
