{
dv为要输出到Excel的数据，str为标题名称
GC.Collect();
Excel.Application excel;// = new Application();
行
int rowIndex = 4;
列
int colIndex = 1;
Excel._Workbook xBk;
Excel._Worksheet xSt;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
取得标题
foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
取得表格中的数据
foreach (DataRowView row in dv)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
{
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
}
加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
取得整个报表的标题
excel.Cells[2, 2] = str;
设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
显示效果
excel.Visible=true;
xSt.Export(Server.MapPath(".")+""""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
xBk.SaveCopyAs(Server.MapPath(".") +this.xlfile.Text + ".xls");
dv = null;
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath(".") + this.xlfile.Text + ".xls";// Server.MapPath(this.xlfile.Text + ".xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
} 以上是导出
public DataTable InputExcel(string Path, string TableName, string tablename2)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";Persist Security Info = FALSE;";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
string strExcel = "select * from [" + TableName + "$]";
OleDbCommand cm1 = new OleDbCommand(strExcel, conn);
OleDbDataAdapter myCommand = new OleDbDataAdapter(cm1);
if (tablename2.Length > 0 && !tablename2.Equals(string.Empty))
TableName = tablename2;
DataTable ds = new DataTable();
Session["fff"] = ds.DefaultView;
conn.Open();
myCommand.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
}以上是导入
4 个解决方案
#1
重新去找一个导出导入的代码
#2
去哪找啊
#3
那可不可以判断导入的EXCEL是03版本的还是07版本的
#4
问题出在导出的方法上，我先前也出现过类似的问题，建议你在去重新找个导出的方法
我这里有个导出的方法，你可以看看
public static void Export(GridView gv, string filename)
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
ColumnInfo cinfo = new ColumnInfo(doc, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 19 * 387;//列宽度
sheet.AddColumnInfo(cinfo);
//填充标题行
Cell cell;
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
cell = sheet.Cells.Add(1, 1 + i, gv.HeaderRow.Cells[i].Text);
}
//填充内容
Cell cll;
for (int i = 0; i < gv.Rows.Count; i++)
{
for (int j = 0; j < gv.Columns.Count; j++)
{
if (gv.Rows[i].Cells[j].Text.ToString().IndexOf("×") >= 0)
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString().Replace("×", "x"));
}
else
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString());
}
cll.Font.FontFamily = FontFamilies.Roman;
//cll= sheet.Cells.Add(i + 2, j + 1, this.grdShop.Rows[i].Cells[j].ToString());
}
}
doc.Send();
}
我这里有个导出的方法，你可以看看
public static void Export(GridView gv, string filename)
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
ColumnInfo cinfo = new ColumnInfo(doc, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 19 * 387;//列宽度
sheet.AddColumnInfo(cinfo);
//填充标题行
Cell cell;
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
cell = sheet.Cells.Add(1, 1 + i, gv.HeaderRow.Cells[i].Text);
}
//填充内容
Cell cll;
for (int i = 0; i < gv.Rows.Count; i++)
{
for (int j = 0; j < gv.Columns.Count; j++)
{
if (gv.Rows[i].Cells[j].Text.ToString().IndexOf("×") >= 0)
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString().Replace("×", "x"));
}
else
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString());
}
cll.Font.FontFamily = FontFamilies.Roman;
//cll= sheet.Cells.Add(i + 2, j + 1, this.grdShop.Rows[i].Cells[j].ToString());
}
}
doc.Send();
}
#1
重新去找一个导出导入的代码
#2
去哪找啊
#3
那可不可以判断导入的EXCEL是03版本的还是07版本的
#4
问题出在导出的方法上，我先前也出现过类似的问题，建议你在去重新找个导出的方法
我这里有个导出的方法，你可以看看
public static void Export(GridView gv, string filename)
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
ColumnInfo cinfo = new ColumnInfo(doc, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 19 * 387;//列宽度
sheet.AddColumnInfo(cinfo);
//填充标题行
Cell cell;
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
cell = sheet.Cells.Add(1, 1 + i, gv.HeaderRow.Cells[i].Text);
}
//填充内容
Cell cll;
for (int i = 0; i < gv.Rows.Count; i++)
{
for (int j = 0; j < gv.Columns.Count; j++)
{
if (gv.Rows[i].Cells[j].Text.ToString().IndexOf("×") >= 0)
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString().Replace("×", "x"));
}
else
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString());
}
cll.Font.FontFamily = FontFamilies.Roman;
//cll= sheet.Cells.Add(i + 2, j + 1, this.grdShop.Rows[i].Cells[j].ToString());
}
}
doc.Send();
}
我这里有个导出的方法，你可以看看
public static void Export(GridView gv, string filename)
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
ColumnInfo cinfo = new ColumnInfo(doc, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 19 * 387;//列宽度
sheet.AddColumnInfo(cinfo);
//填充标题行
Cell cell;
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
cell = sheet.Cells.Add(1, 1 + i, gv.HeaderRow.Cells[i].Text);
}
//填充内容
Cell cll;
for (int i = 0; i < gv.Rows.Count; i++)
{
for (int j = 0; j < gv.Columns.Count; j++)
{
if (gv.Rows[i].Cells[j].Text.ToString().IndexOf("×") >= 0)
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString().Replace("×", "x"));
}
else
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString());
}
cll.Font.FontFamily = FontFamilies.Roman;
//cll= sheet.Cells.Add(i + 2, j + 1, this.grdShop.Rows[i].Cells[j].ToString());
}
}
doc.Send();
}