简单说Excel 里Sheet1里的数据是 A B C D E.....
1 分数
2 编号 姓名 语文 数学
3 1 aa 40 40
4 2 bb 50 50
. 3 cc 60 30
.
.我需要用程序实现格式转换一下,就是把标题加到每一行的上面,格式为 A B C D E.....
1 分数
2 编号 姓名 语文 数学
3 1 aa 40 40
4 分数
6 编号 姓名 语文 数学
6
7 2 bb 50 50
8 分数
9 编号 姓名 语文 数学
. 3 cc 60 30
.这样的格式。
完全不熟悉excel 的操作,有熟悉的路过的帮着看一下。解决了就结贴
1 分数
2 编号 姓名 语文 数学
3 1 aa 40 40
4 2 bb 50 50
. 3 cc 60 30
.
.我需要用程序实现格式转换一下,就是把标题加到每一行的上面,格式为 A B C D E.....
1 分数
2 编号 姓名 语文 数学
3 1 aa 40 40
4 分数
6 编号 姓名 语文 数学
6
7 2 bb 50 50
8 分数
9 编号 姓名 语文 数学
. 3 cc 60 30
.这样的格式。
完全不熟悉excel 的操作,有熟悉的路过的帮着看一下。解决了就结贴
2 在dataset中找到数据和要复制的列名
3 构建所需结果的dataset
4 用Excel.Application等Excle API 将Excle回写保存回去
可以把Excel当作数据库来处理也可以用操作Cell的办法一个一个的写http://www.cnblogs.com/emanlee/archive/2007/05/31/766520.aspxhttp://www.cnblogs.com/king_astar/archive/2006/02/17/332785.aspx
郁闷了。
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
oXL = new Excel.Application();
oXL.Visible = true; //建立表
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet; //向单元格里面写数据
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
//打开xls文件
Excel.Workbook xBook = ep.Workbooks._Open(@"D:\***.xls",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ApplicationClass saveEx = new ApplicationClass();
saveEx.Visible =true;
saveEx.UserControl = true;
Workbooks newWork = saveEx.Workbooks;
_Workbook workBook = newWork.Add(XlWBATemplate.xlWBATWorksheet);Sheets sheets = workBook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);int maxCount = 最大行数;
for(int i = 0;i < maxCount;i++)
{
int row = i*3;
worksheet.get_Range("A" + Convert.ToString(row+1), "D" + Convert.ToString(row+1)).Merge(0);
worksheet.get_Range("A" + Convert.ToString(row+1), "D" + Convert.ToString(row+1)).VerticalAlignment = -4108;
worksheet.get_Range("A" + Convert.ToString(row+1), "D" + Convert.ToString(row+1)).HorizontalAlignment = -4108; worksheet.get_Range("A" + Convert.ToString(row+1), "D" + Convert.ToString(row+1)).Value2 = "分数";
worksheet.get_Range("A" + Convert.ToString(row+2), "A" + Convert.ToString(row+2)).Value = "编号";
worksheet.get_Range("B" + Convert.ToString(row+2), "B" + Convert.ToString(row+2)).Value = "姓名";
worksheet.get_Range("C" + Convert.ToString(row+2), "C" + Convert.ToString(row+2)).Value = "语文";
worksheet.get_Range("D" + Convert.ToString(row+2), "D" + Convert.ToString(row+2)).Value = "数学";
//以下 的各个值可以在循环里面获得,我就不写了
worksheet.get_Range("A" + Convert.ToString(row+3), "A" + Convert.ToString(row+3)).Value = "编号的值";
worksheet.get_Range("B" + Convert.ToString(row+3), "B" + Convert.ToString(row+3)).Value = "姓名的值";
worksheet.get_Range("C" + Convert.ToString(row+3), "C" + Convert.ToString(row+3)).Value = "语文的值";
worksheet.get_Range("D" + Convert.ToString(row+3), "D" + Convert.ToString(row+3)).Value = "数学的值";
}应该能达到你的效果
引用这个命名空间:using Excel = Microsoft.Office.Interop.Excel;
public class ClsOprExcel
{
private Excel.ApplicationClass m_appThisApplication = null;
private Excel.Workbook m_ex_workbook_wb = null;
private Excel._Worksheet m_objSheet = null;
public int m_intcolsNos = 0;
private Excel.Range m_Rangep = null; #region Properity
public Excel.ApplicationClass ThisApplication
{
get
{
return m_appThisApplication;
}
set
{
m_appThisApplication = value;
}
} /// <summary>
///
/// </summary>
public Excel.Workbook ThisWorkbook
{
get
{
return m_ex_workbook_wb;
}
set
{
m_ex_workbook_wb = value;
}
} /// <summary>
///
/// </summary>
public Excel._Worksheet ThisWorksheet
{
get
{
return m_objSheet;
}
set
{
m_objSheet = value;
}
} #endregion
public ClsOprExcel()
{
try
{
m_appThisApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
}
catch (Exception exception)
{
if (m_appThisApplication != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_appThisApplication);
m_appThisApplication = null;
}
throw exception;
}
} private void GetWorkSheet(int p_intitemCount)
{
m_objSheet = (Excel._Worksheet)(m_ex_workbook_wb.Worksheets.get_Item(p_intitemCount));
} private void GetWorkBook()
{
m_ex_workbook_wb = m_appThisApplication.Workbooks.Add(Type.Missing);
} /// <summary>
/// 打开一个工作簿 无参数传入新建一个WorkBook
/// </summary>
/// <param name="filename">打开的工作簿路径</param>
public void GetWorkBook(string filename)
{
m_ex_workbook_wb = m_appThisApplication.Workbooks.Open(
@filename,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
} /// <summary>
/// 获取一个工作区域
/// </summary>
/// <param name="filename">打开工作簿路径</param>
/// <param name="startStr">Excle开始区域 格式“A1”</param>
/// <param name="endStr">Excle结束区域 格式“A1”</param>
/// <param name="WorksheetsItem">Worksheets的索引</param>
public void GetRange(string filename, string startStr, string endStr, int WorksheetsItem)
{
GetWorkBook(filename);
GetWorkSheet(WorksheetsItem);
m_Rangep = m_objSheet.get_Range(startStr, endStr);
} public void CloseAll()
{
m_objSheet = null;
m_ex_workbook_wb.Close(false, Type.Missing, Type.Missing);
m_appThisApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_appThisApplication);
m_appThisApplication = null;
GC.Collect();
} /// <summary>
/// 添加表格框
/// </summary>
/// <param name="p_StartRow">开始行</param>
/// <param name="p_StartCol"></param>
/// <param name="p_EndRow"></param>
/// <param name="p_EndCol"></param>
public void SetAreaBorder(int p_StartRow, int p_StartCol, int p_EndRow, int p_EndCol)
{
Excel.XlBordersIndex[] edgeType = new Excel.XlBordersIndex[6]{Excel.XlBordersIndex.xlEdgeBottom,Excel.XlBordersIndex.xlEdgeLeft,
Excel.XlBordersIndex.xlEdgeRight,Excel.XlBordersIndex.xlEdgeTop,
Excel.XlBordersIndex.xlInsideHorizontal,Excel.XlBordersIndex.xlInsideVertical};
for (int i = 0; i < edgeType.Length; i++)
{
m_objSheet.get_Range(m_objSheet.Cells[p_StartRow, p_StartCol], m_objSheet.Cells[p_EndRow, p_EndCol]).Borders[edgeType[i]].LineStyle = Excel.XlLineStyle.xlContinuous;
m_objSheet.get_Range(m_objSheet.Cells[p_StartRow, p_StartCol], m_objSheet.Cells[p_EndRow, p_EndCol]).Borders[edgeType[i]].Weight = Excel.XlBorderWeight.xlThin;
m_objSheet.get_Range(m_objSheet.Cells[p_StartRow, p_StartCol], m_objSheet.Cells[p_EndRow, p_EndCol]).Borders[edgeType[i]].ColorIndex = Excel.Constants.xlAutomatic;
}
}
/// <summary>
/// 保存信息到Excel
/// </summary>
public void Save(DataTable dtSource, string p_title)
{
if (dtSource == null || dtSource.Rows.Count == 0 || dtSource.Columns.Count == 0)
{
return null;
}
int colNo = dtSource.Columns.Count;
int rowNo = dtSource.Rows.Count; int startindexNo = 1; ThisApplication.DisplayNoteIndicator = false;
GetWorkBook();
GetWorkSheet(1);
Excel.Range m_rang = null;
if (p_title != null && p_title != "")
{ ThisApplication.Cells[1, 1] = p_title;
m_rang = ThisWorksheet.get_Range(ThisApplication.Cells[1, 1], ThisApplication.Cells[3, colNo]);
m_rang.HorizontalAlignment = Excel.Constants.xlCenter;
m_rang.Font.Size = 18;
m_rang.Font.Bold = true;
m_rang.MergeCells = true;
startindexNo = 3;
}
else
{
m_rang = ThisWorksheet.get_Range(ThisApplication.Cells[1, 1], ThisApplication.Cells[1, colNo]);
m_rang.HorizontalAlignment = Excel.Constants.xlCenter;
m_rang.MergeCells = true;
startindexNo = 1;
}
if (colNo > 256)
{
colNo = 256;
}
if (rowNo > 65536)
{
rowNo = 65536;
}
object m_cush = null;
Excel.Range m_rangs;
for (int p_rows = 0; p_rows < rowNo; p_rows++)//行循环
{
for (int p_colss = 0; p_colss < colNo; p_colss++)//列循环
{
m_cush = dtSource.Rows[p_rows][p_colss].ToString();
ThisApplication.Cells[p_rows + startindexNo + 1, p_colss + 1] = dtSource.Rows[p_rows][p_colss].ToString();
}
}
SetAreaBorder(startindexNo, 1, rowNo + startindexNo, colNo);
string filename = "D:\\XXX";//文件全路径
ThisWorkbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
if (m_rang != null)
m_rang.Clear();
CloseAll();
}