作者：乔山办公网日期：
返回目录：excel表格制作
你可以直接在m文件里面用xlswrite函数：（filename/sheet/range都是要加单引号滴）
xlswrite(filename, M)；将矩阵M的数据写入名为filename的Excel文件中。
xlswrite(filename, M, sheet)；将矩阵M的数据写入文件名为filename中的指定的sheet中。
xlswrite(filename, M, range)；将矩阵M中的数据写入文件名为filename的Excel文件中，且由range制定存储的区域，例如'C1:C2'.
xlswrite(filename, M, sheet, range) ；在上一条命令的基础上指定了所要存储的sheet。
status = xlswrite(filename, ...) ；返回完成状态值。如果写入成功，则status为1;反之写入失败，则status为0.
[status, message] = xlswrite(filename, ...)；返回由于写入操作而产生的任何错误或警告信息e68a84e8a2ade799bee5baa6e79fa5e98193361
应用举例
例一：将数据写入默认的工作表中
将一七元素向量写入testdata.xls中。默认格式下，数据将写入文件中第一个工作表的A1至G1单元格。 xlswrite('testdata.xls', [12.7 5.02 -98 63.9 0 -.2 56])
例二：将混合数据写入制定工作表中
d = {'Time', 'Temp'; 12 98; 13 99; 14 97};
s = xlswrite('tempdata.xls', d, 'Temperatures', 'E1')
s =
1
Time Temp
12 98
13 99
14 97
例三：向文件中添加新的工作表
现将上例中的数据写入tempdata.xls中并不存在的一个工作表中，在这种情况下，xlswrite会添加一个新的工作表，名称由用户指定，此时xlswrite会显示警告提示添加了新的工作表。xlswrite('tempdata.xls', d, 'NewTemp', 'E1')
Warning: Added specified worksheet.
如果不想看到这些警告，可以输入下列命令。
warning off MATLAB:xlswrite:AddSheet
再一次输入写入命令，这次创建另外一个新的工作表NewTemp2，此时不再显示提示信息，但仍可以用msg命令提取该信息。
[stat msg] = xlswrite('tempdata.xls', d, 'NewTemp2', 'E1');
msg
msg =
message: 'Added specified worksheet.'
identifier: 'MATLAB:xlswrite:AddSheet' book.
#region 将DataGridView控件中数据导出到e79fa5e98193e78988e69d83335Excel
/// <summary>
/// 将DataGridView控件中数据导出到Excel
/// </summary>
/// <param name="gridView">DataGridView对象</param>
/// <param name="isShowExcle">是否显示Excel界面</param>
/// <returns></returns>
public static bool ExportDataGridview(DataGridView gridView, bool isShowExcle, string path)
{
bool result = false;
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook myWorkBook = null;
try
{
if (gridView.Rows.Count == 0)
return false;
//建立Excel对象
excel = new Microsoft.Office.Interop.Excel.Application();
myWorkBook = excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名称
for (int i = 0; i < gridView.ColumnCount; i++)
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
//填充数据
for (int i = 0; i < gridView.Rows.Count - 1; i++)
{
for (int j = 0; j < gridView.Columns.Count; j++)
{
if (gridView[j, i].ValueType == typeof(string))
excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
else
excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
}
}
object missing = System.Reflection.Missing.Value;
excel.ActiveWorkbook.Saved = true;
excel.ActiveWorkbook.SaveAs(path,
Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
missing, missing, false,
false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
myWorkBook.Close(missing, missing, missing);
myWorkBook = null;
excel.Quit();
result = true;
}
catch (System.Exception ex)
{
excel.Quit();
XException xe = FuncLib.BuildXExcpetion("将DataGridView控件中数据导出到Excel出错", ex);
xe.AddEnvironmentData("path", path, true);
xe.WriteLogRecord(false, true, DateTime.Now.ToString("yyyy-MM-dd"));
}
return result;
}
按条件查询（支持组合查询）在gridview里已经能显示出来，需要导入excel和页面打印的功能，请大虾们帮帮忙~~~
有两个方法,一个是将datatable转为excel,一个是将DataGridView 导出为excel
将dataset导出为excel也很简单,遍历下dataset.tables.
呈现乱码的是我为自己的程序定制的,你可以不加.
public static bool tableinfo_to_excel(System.Data.DataTable dt, string fileName, bool isShowExcle)
{
DataSet ds = get_dataset(dt);
if (ds == null)
{
return false;
}
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return false;
}
app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Workbook workbook1 = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Worksheet worksheet.
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
//_Worksheet worksheet = (_Worksheet)sheets.Item(1);
worksheet.Name = "惆豢";
if (worksheet == null)
{
return false;
}
int i_row = 0;
foreach (DataRow dr in dt.Rows)
{
string sLen = "";
string s = (string)dr["heardtext"];
string[] s_headtext = s.Split(',');
char H = (char)(64 + s_headtext.Length / 26);
char L = (char)(64 + s_headtext.Length % 26);
if (s_headtext.Length < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
i_row += 1;
string sTmp = sLen + i_row.ToString();
Range ranCaption = worksheet.get_Range(sTmp, "A" + i_row);
//ranCaption.Merge(true);
ranCaption.MergeCells = true;
ranCaption.RowHeight = 30;
//ranCaption.Font.Color=3;
ranCaption.Font.Color = 255;
ranCaption.Font.Bold = true;
ranCaption.Font.Size = 14;
ranCaption.Borders.Color = 255;
//ranCaption.Borders.LineStyle = XlLineStyle.xlDouble;
//ranCaption.Borders.Value = -4119;
ranCaption.Borders.Weight = 3;
//ranCaption.HorizontalAlignment=2;
//object lkjsdklf=ranCaption.Orientation;
//object lkjsdklf = ranCaption.Style;
ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;//懈笢
//ranCaption.Font.ColorIndex = 16777215;
//ranCaption.Font.ColorIndex = -2000;
//font_my.Bold =true;
//font_my.Background=XlBackground.xlBackgroundOpaque;
// ranCaption.AutoFormat(XlRangeAutoFormat.xlRangeAutoFormatNone, 2, font_my, "Center", true, null, 80);
ranCaption.Value2 = (string)dr["table_name"];
i_row += 1;
sTmp = sLen + i_row.ToString();
ranCaption = worksheet.get_Range(sTmp, "A" + i_row);
ranCaption.Value2 = s_headtext;
ranCaption.Borders.Color = 0;
ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Data.DataTable s_dt = ds.Tables[(string)dr["table_name"]];
object[] obj = new object[s_dt.Columns.Count];
//int i_date_index=new int();
foreach (DataRow dr_jkdj in s_dt.Rows)
{
for (int l = 0; l < s_dt.Columns.Count; l++)
{
if (dr_jkdj[l].GetType() == typeof(DateTime))
{
obj[l] = dr_jkdj[l].ToString();
string cell11 = sLen + ((int)(l + 1)).ToString();
string cell21 = "A" + ((int)(l + 1)).ToString();
Range ran1 = worksheet.get_Range(cell11, cell21);
Range cellrange = (Range)ran1.Cells[0, l + 1];
cellrange.ColumnWidth = 14;
}
//蚚誧赻隅砱氝楼
else if (dr_jkdj[l].GetType() == typeof(Boolean))
{
DateTime dt_jl = (DateTime)dr_jkdj[l - 2];
int i_day = (int)dr_jkdj[l - 1];
if (dt_jl.AddDays(i_day) < DateTime.Now)
{
obj[l] = "峎党闭";
}
else
obj[l] = "淏婓峎党";
}
//蚚誧赻隅砱氝楼赋旰
else
obj[l] = dr_jkdj[l];
}
string cell1 = sLen + ((int)(i_row + 1)).ToString();
string cell2 = "A" + ((int)(i_row + 1)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
//cellrange.Columns=20;
ran.Borders.Color = 0;
i_row++;
ran.Value2 = obj;
}
i_row = i_row + 2;
}
if (!isShowExcle)
{
workbook.Password = "18356771";
}
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
app.UserControl = false;
app.Quit();
return true;
}
catch(Exception ee)
{
app.UserControl = false;
app.Quit();
return false;
}
}
public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle)
{
//膘蕾?????勤砓
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return false;
}
app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Workbook workbook1 = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Worksheet worksheet.
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
//_Worksheet worksheet = (_Worksheet)sheets.Item(1);
if (worksheet == null)
{
return false;
}
string sLen = "";
//腕郔缀珨蹈蹈靡
char H = (char)(64 + gridView.ColumnCount / 26);
char L = (char)(64 + gridView.ColumnCount % 26);
if (gridView.ColumnCount < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//梓枙
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption;
//杅擂7a64e78988e69d83361
object[] obj = new object[gridView.Columns.Count];
for (int r = 0; r < gridView.RowCount; r++)
{
for (int l = 0; l < gridView.Columns.Count; l++)
{
if (gridView[l, r].ValueType == typeof(DateTime))
{
obj[l] = gridView[l, r].Value.ToString();
}
else
{
obj[l] = gridView[l, r].Value;
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//怅湔
if (!isShowExcle)
{
workbook.Password = "18356771";
}
workbook.SaveCopyAs(fileName);
//workbook.SaveAs(fileName, ".xls", "18356771", false, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, false, false, false, false);
workbook.Saved = true;
}
finally
{
//寿敕
app.UserControl = false;
app.Quit();
}
return true;
}